CODEWITHSUNDEEP
mysqlsql
Sourabh Kumar
Sourabh Kumar

Reputation: 94

Fetching the sub-string from the database

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

[email protected]

[email protected]

[email protected]

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.

Upvotes: 0

Views: 83

Answers (2)

Dag Sondre Hansen
Dag Sondre Hansen

Reputation: 2499

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. "[email protected]" 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").

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269883

substring_index() is the best approach:

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

Upvotes: 0

Related Questions