locked
String Manipulation RRS feed

  • Question

  • 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


    ebro

    Thursday, April 30, 2015 4:05 PM

Answers

  • 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
    Thursday, April 30, 2015 4:32 PM
  • -- 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'


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

    • Marked as answer by ebrolove Thursday, April 30, 2015 5:35 PM
    Thursday, April 30, 2015 4:42 PM

All replies

  • 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
    Thursday, April 30, 2015 4:32 PM
  • -- 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'


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

    • Marked as answer by ebrolove Thursday, April 30, 2015 5:35 PM
    Thursday, April 30, 2015 4:42 PM
  • AWESOME GUYS!!

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Saturday, May 9, 2015 4:40 PM