Mysql Query

Question

Hi all i'm writing a simple Mysql query to pull out some CSV but the where clause don't seem to work any ideas would be a great assist

select SUBSTRING_INDEX(email,'@',1)AS email , clear , SUBSTRING(email,LOCATE('@',email)+1) as domain where domain like 'somestring'  from sometable;

Answer 1

SELECT 
  SUBSTRING_INDEX(email,'@',1) AS email,
  clear, 
  SUBSTRING(email,LOCATE('@',email)+1) AS domain 
FROM sometable
WHERE domain LIKE '%somestring%';

Answer 2

John's code should be right. My only addition is to always review your SQL syntax when your SQL doesn't work.

You'll improve your ability to write complex SQL if you carefully logic though it then to simply write something then post it on here. I'm not saying you did that but your SQL looks all mixed up... It looks like you didn't verify your syntax...

In regards to your comment to John that the domain is invalid: Is 'Domain' the correct field name? If not, what is the correct field name and can you insert it. I think the code should be:

SELECT 
  SUBSTRING_INDEX(email,'@',1) AS email,
  clear, 
  SUBSTRING(email,LOCATE('@',email)+1) AS domain 
FROM sometable
WHERE email LIKE '%@somestring%';

Or

SELECT 
  SUBSTRING_INDEX(email,'@',1) AS email,
  clear, 
  SUBSTRING(email,LOCATE('@',email)+1) AS domain 
FROM sometable
WHERE SUBSTRING(email,LOCATE('@',email)+1) LIKE '%somestring%';

Regards,
Frank