Fetching the sub-string from the database

问题:

I have a table with a column which contains strings like below.

[e-mail]

[e-mail]

[e-mail]

I need to get the substring from the @ to .(dot) i have writing some sql but it have fixed length i need the dynamic query to get the sub-string.

select *from registration where email like '%@%';

Sub string Query select email, substr(email,4,10) from registration;

please and one write query for me.

回答1:

This should do it:

SELECT REVERSE(SUBSTRING(REVERSE(SUBSTRING_INDEX(email, '@', -1)), LOCATE('.', REVERSE(email))+1));

This will include all dots between @ and the final one (e.g. "[e-mail]" will result in "a.long.domain.name") as opposed to nested SUBSTRING_INDEX which will only return whats between @ and the first dot (e.g "a").

回答2:

substring_index() is the best approach:

select substring_index(substring_index(email, '@', -1), '.', 1)

回答3:

Try using nested SUBSTring_INDEX function. http://www.w3resource.com/mysql/string-functions/mysql-substring_index-function.php