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;
SELECT
SUBSTRING_INDEX(email,'@',1) AS email,
clear,
SUBSTRING(email,LOCATE('@',email)+1) AS domain
FROM sometable
WHERE domain LIKE '%somestring%';
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
This is the correct query. Im assuming clear is one of the field from that table.
SELECT substring_index(email, '@', 1) as email,clear, substring(email, locate('@',email)+1) as domain from table where substring(email, locate('@',email)+1) like '%somestring%'.
select SUBSTRING_INDEX(email,'@',1)AS email , clear , SUBSTRING(email,LOCATE('@',email)+1) as domain from sometable where domain like '%somestring%'
like operator must use with "%" character.
