Stonesmit Stonesmit - 5 months ago 11
MySQL Question

MySQL slpited string in condition

I have the following sql questy


SELECT Name,Email
FROM customer
WHERE Email LIKE '%SUBSTRING_INDEX(SUBSTRING_INDEX(Name, ' ', 1), ' ', -1)%'
OR Email LIKE '%SUBSTRING_INDEX(SUBSTRING_INDEX(Name, ' ', 2), ' ', -1)%';


In the Name collonb I have names like "Gearge Martin" and i like to check if there is any similarity between the Names and Email addresses. But this query doesn't give any records back.

Any suggestions?
Thanks

Answer

Use WHERE Email LIKE CONCAT(...):

SELECT Name, Email
FROM customer 
WHERE Email LIKE CONCAT('%', SUBSTRING_INDEX(SUBSTRING_INDEX(Name, ' ', 1), ' ', -1), '%') OR
      Email LIKE CONCAT('%', SUBSTRING_INDEX(SUBSTRING_INDEX(Name, ' ', 2), ' ', -1), '%');

You were trying to compare the Email field against the literal string 'SUBSTRING_INDEX(...' which isn't what you want.