String Manipulation

Hi gurus,

i want to extract only say the emails that have '@aol.com' in the below string. so if I give you the below string how can I pick only the emails with the domain  '@aol.com'

for example, from the below I want only chanc@aol.com; boka@aol.com; 

obama@teleson-net.de; chanc@aol.com; rimond@hotmail.fr;  boka@aol.com; mariana@yahoo.com; 

declare @t table (id int, emails nvarchar(max))
insert into @t values (1, 'obama@teleson-net.de; chanc@aol.com; rimond@hotmail.fr;  boka@aol.com; mariana@yahoo.com;')

thanks in advance

April 30th, 2015 4:05pm

declare @t table (id int, emails nvarchar(max))
insert into @t values (1, 'obama@teleson-net.de; chanc@aol.com; rimond@hotmail.fr;  boka@aol.com; mariana@yahoo.com;')



;with mycte as (
SELECT   id, S.a.value('.', 'VARCHAR(100)') AS splitVal
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(emails, ';', '</r><r>')+ '</r></H>' AS XML) AS [vals]
FROM @t) d
  
CROSS APPLY d.[vals].nodes('/H/r') S(a)

)


Select id,
       Stuff(( SELECT ',' + Cast(t2.splitVal as varchar(50))
           FROM mycte t2
          WHERE t2.ID = t1.ID  and  splitVal like '%@aol.com'
          ORDER BY id
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS emails
  FROM mycte t1
  
 GROUP BY t1.ID 



 

  • Marked as answer by ebrolove Thursday, April 30, 2015 5:35 PM
Free Windows Admin Tool Kit Click here and download it now
April 30th, 2015 4:32pm

-- Function splitter - returns a table of split values on delim-------------------------
CREATE FUNCTION [dbo].[splitter](@string VARCHAR(MAX), @delim CHAR(1))
RETURNS @result TABLE (id INT IDENTITY, value VARCHAR(MAX))
AS
BEGIN
 WHILE CHARINDEX(@delim,@string) > 0
  BEGIN
   INSERT INTO @result (value) VALUES (LEFT(@string,CHARINDEX(@delim,@string)-1))
   SET @string = RIGHT(@string,LEN(@string)-CHARINDEX(@delim,@string))
  END
   INSERT INTO @result (value) VALUES (@string)

RETURN
END
----------------------------------------------------------------------------------------

declare @t table (id int, emails nvarchar(max))
insert into @t values (1, 'obama@teleson-net.de; chanc@aol.com; rimond@hotmail.fr;  boka@aol.com; mariana@yahoo.com;')
 
 ----------------------------------------------------------------------------------------

SELECT *
  FROM @t
    CROSS APPLY dbo.splitter(emails,';') e
 WHERE value LIKE '%@aol.com'

April 30th, 2015 4:42pm

AWESOME GUYS!!
Free Windows Admin Tool Kit Click here and download it now
May 9th, 2015 12:47pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics