Parse out all email addresses in a string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Parse out all email addresses in a string

Author

Topic

Rich75
Starting Member

8 Posts

Posted - 2013-07-23 : 12:22:28

I have a string like this one in my column

Mike@yahoo.com, Bill@aol.com, Dan@yahoo.com, Frank@gmail.com

In my result set I need to display all email addresses that do not have the @yahoo.com domain. Could anyone suggest how to do this?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-23 : 12:37:14

How do you suggest we write the code so the display thingy is according to your idea?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-23 : 12:50:04

Is this what you are looking for:
[CODE]

-- TEST DATA
DECLARE @Temp TABLE(email VARCHAR(60));
INSERT INTO @Temp VALUES
('Mike@yahoo.com'),
('Bill@aol.com'),
('Dan@yahoo.com'),
('Frank@gmail.com');

SELECT * from @Temp where email not like '%@yahoo.com';

[/CODE]

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-23 : 13:04:25

If you have a comma separated list may be this solution:
[CODE]

DECLARE @s VARCHAR(255) = 'Mike@yahoo.com, Bill@aol.com, Dan@yahoo.com, Frank@gmail.com';
SELECT STUFF(c,1,1,'') AS emails
FROM
(
SELECT ','+Item FROM
dbo.DelimitedSplit8K(@s,',')
WHERE
Item NOT LIKE '%@yahoo.com'
FOR XML PATH('')
) T(c);

[/CODE]

EDITED: Source code for dbo.DelimitedSplit8K() can be found here:
Figure 21 in this article: http://www.sqlservercentral.com/articles/Tally+Table/72993/

Rich75
Starting Member

8 Posts

Posted - 2013-07-23 : 14:03:55

quote:
Originally posted by MuMu88

If you have a comma separated list may be this solution:
[CODE]

DECLARE @s VARCHAR(255) = 'Mike@yahoo.com, Bill@aol.com, Dan@yahoo.com, Frank@gmail.com';
SELECT STUFF(c,1,1,'') AS emails
FROM
(
SELECT ','+Item FROM
dbo.DelimitedSplit8K(@s,',')
WHERE
Item NOT LIKE '%@yahoo.com'
FOR XML PATH('')
) T(c);

[/CODE]

EDITED: Source code for dbo.DelimitedSplit8K() can be found here:
Figure 21 in this article: http://www.sqlservercentral.com/articles/Tally+Table/72993/

Thanks a lot

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-23 : 14:28:03

[code]DECLARE @Sample VARCHAR(MAX) = 'Mike@yahoo.com, Bill@aol.com, Dan@yahoo.com, Frank@gmail.com';

SELECT f.n.value('.', 'VARCHAR(MAX)') AS Email
FROM (
SELECT CAST('<m>' + REPLACE(@Sample, ', ', '</m><m>') + '</m>' AS XML) AS Data
) AS d
CROSS APPLY d.Data.nodes('m') AS f(n)
WHERE f.n.value('.', 'VARCHAR(MAX)') NOT LIKE '%@yahoo.com';[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-23 : 14:32:44

quote:

Originally posted by SwePeso

DECLARE @Sample VARCHAR(MAX) = 'Mike@yahoo.com, Bill@aol.com, Dan@yahoo.com, Frank@gmail.com';

SELECT		f.n.value('.', 'VARCHAR(MAX)') AS Email
FROM		(
			SELECT	CAST('<m>' + REPLACE(@Sample, ', ', '</m><m>') + '</m>' AS XML) AS Data
		) AS d
CROSS APPLY	d.Data.nodes('m') AS f(n)
WHERE		f.n.value('.', 'VARCHAR(MAX)') NOT LIKE '%@yahoo.com';

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

While insignificant for a single variable such as this, if the intent is to extend this to large amounts of data (stored in a table), constructing and then shredding the XML this way would be much more resource intensive than the method Mumu posted.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-07-28 : 11:41:22

Normalise the table and store each email as separate row so that your query is so easy like

where email not like '%@yahoo.com'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources