Selectively Extract emails out of columns

Question

declare @T table (id int, fname varchar(50), tname varchar(50),  email varchar(200))

insert into @T values (1, 'fname1', 'tname1', 'xxxxxx@msn.com   xxx.xxxxx.com')
insert into @T values (2, 'fname2', 'tname2', 'just a bunch of usless data')
insert into @T values (3, 'fname3', 'tname3', 'xxx@xx.xx.com')
insert into @T values (4, 'fname4', 'tname4', 'xxx@xx.xx.com xxx@yahoo.com')
insert into @T values (5, 'fname5', 'tname5', 'xxx@gmail.com xxxx.msn.com')
insert into @T values (3, 'fname6', 'tname6', 'xxx@msn.com')

I have a table with data as shown above, some good emails some bad..

i need to select this data into a new temporary table that i will genereate emails from, I need to create one row for each valid email address, so i would duplicate fname, tname, email for each valid email. I don't mind if i get a row with a bad email address, as long as i get a row for each valid one.

Could someone help with that select query?

I would appreciate it. Thanks

SQL-Server 2008 provides no built-in split string function, so you need a custom function like this: http://stackoverflow.com/a/10914602/2947592 — wvdz, Nov 12 '13 at 16:41

score 3 · Answer 1 · answered Nov 12 '13 at 17:07

First, create a simple string splitting function:

CREATE FUNCTION [dbo].[SplitString]
    (
        @List NVARCHAR(MAX),
        @Delim VARCHAR(255)
    )
    RETURNS TABLE
    AS
        RETURN ( SELECT [Value] FROM 
          ( 
            SELECT 
              [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
              CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
            FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
              FROM sys.all_objects) AS x
              WHERE Number <= LEN(@List)
              AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
          ) AS y
        );

Then, you can use it in an OUTER APPLY:

declare @T table (id int, fname varchar(50), tname varchar(50),  email varchar(200))

insert into @T values (1, 'fname1', 'tname1', 'xxxxxx@msn.com   xxx.xxxxx.com')
insert into @T values (2, 'fname2', 'tname2', 'just a bunch of usless data')
insert into @T values (3, 'fname3', 'tname3', 'xxx@xx.xx.com')
insert into @T values (4, 'fname4', 'tname4', 'xxx@xx.xx.com xxx@yahoo.com')
insert into @T values (5, 'fname5', 'tname5', 'xxx@gmail.com xxxx.msn.com')
insert into @T values (3, 'fname6', 'tname6', 'xxx@msn.com')

SELECT t.id, t.fname, t.tname, f.Value
  FROM @T AS t
  OUTER APPLY dbo.SplitString(REPLACE(t.email, ' ', ';'), ';') AS f
  WHERE t.email LIKE '%@%' AND f.Value LIKE '%@%';

Results:

id   fname     tname     Value
---- ------    ------    --------------
1    fname1    tname1    xxxxxx@msn.com
3    fname3    tname3    xxx@xx.xx.com
4    fname4    tname4    xxx@xx.xx.com
4    fname4    tname4    xxx@yahoo.com
5    fname5    tname5    xxx@gmail.com
3    fname6    tname6    xxx@msn.com

More on split functions (and better alternatives, if splitting strings coming from the application layer):

http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings

http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql

Selectively Extract emails out of columns

1 Answers1