locked
extract string RRS feed

  • Question

  • hi

    I need to extract the values for the following from the string listed below and place each in its own column in another table:

    brandRef - PSKO1
    webId - MOONCU1676747
    clientref - UBM
    sourceSystem- Moon

    brandRef=PSKO1&webId=MOONCU1676747&clientRef=UBM%2520INFORMATION%2520LTD%2520test&sourceSystem=Moon

    Is it possible to us TSQL to do this?


    sukai

    Wednesday, April 8, 2015 3:26 PM

Answers

  • declare @test table (Col2  varchar(2000))
    Insert into @test values
    ('brandRef=PSKO1&webId=MOONCU1676747&clientRef=UBM%2520INFORMATION%2520LTD%2520test&sourceSystem=Moon')
    ,('brandRef=PSKO2&webId=MOONCU1676748&clientRef=UBM%2520INFORMATION%2520LTD%2520test2&sourceSystem=Sun')
      
      
     ;with mycte as (
       
    SELECT
    ROW_NUMBER() OVER (ORDER BY S.a.value('count(.)', 'tinyint')) rn,
     
    S.a.value('@brandRef',  'varchar(50)') as brandRef , 
    S.a.value('@webId', 'varchar(50)') as webId , 
    S.a.value('@clientRef', 'varchar(50)') as clientRef
    ,S.a.value('@sourceSystem', 'varchar(50)') as sourceSystem
      
     FROM   (
    SELECT Col2,   
    Cast(N'<H><r  ' + Replace(Replace(Col2, '&','" '),'=','="')  + '" /></H>' as XML)  AS [vals]
    FROM   @test)   d    
    CROSS APPLY d.[vals].nodes('/H/r') S(a) 
     )
      
       
      
    select * from mycte  
     

    • Proposed as answer by Eric__Zhang Wednesday, April 15, 2015 1:37 AM
    • Marked as answer by Eric__Zhang Monday, April 20, 2015 2:34 AM
    Wednesday, April 8, 2015 3:47 PM
  • Obviously there are quite a few ways to do this. Here is another one...

    DECLARE @String VARCHAR(100) = 'brandRef=PSKO1&webId=MOONCU1676747&clientRef=UBM%2520INFORMATION%2520LTD%2520test&sourceSystem=Moon'
    
    ;WITH SplitString AS (
    	SELECT 
    		REPLACE(Item, '=', '.') AS Item
    	FROM 
    		dbo.SplitCSVToTable8K(@String, '&') -- Use the string splitting function of your choice...
    ), ParseStrings AS (
    	SELECT 
    		PARSENAME(ss.Item, 2) AS Col,
    		CASE WHEN PARSENAME(ss.Item, 1) LIKE '%\%%' ESCAPE '\' THEN LEFT(PARSENAME(ss.Item, 1), PATINDEX('%[%]%', PARSENAME(ss.Item, 1))-1) ELSE PARSENAME(ss.Item, 1) END AS Val
    	FROM SplitString ss
    ) 
    	SELECT 
    		MAX(CASE WHEN ps.Col = 'brandRef' THEN ps.Val END) AS [brandRef],
    		MAX(CASE WHEN ps.Col = 'webId' THEN ps.Val END) AS [webId],
    		MAX(CASE WHEN ps.Col = 'clientRef' THEN ps.Val END) AS [clientRef],
    		MAX(CASE WHEN ps.Col = 'sourceSystem' THEN ps.Val END) AS [sourceSystem]
    	FROM ParseStrings ps

    HTH,

    Jason


    Jason Long

    • Proposed as answer by Eric__Zhang Wednesday, April 15, 2015 1:37 AM
    • Marked as answer by Eric__Zhang Monday, April 20, 2015 2:34 AM
    Wednesday, April 8, 2015 5:08 PM
  • Hello Rainbow55,

    Here's another approach, inspired by Jingyang's.

    DECLARE @xml XML
    DECLARE @Str VARCHAR(MAX)
    
    SET @Str='brandRef=PSKO1&webId=MOONCU1676747&clientRef=UBM%2520INFORMATION%2520LTD%2520test&sourceSystem=Moon'
    
    SELECT @xml= CAST(N'<H><r  ' + Replace(Replace(@Str, '&','" '),'=','="')  + '" /></H>' as XML) 
    
    SELECT @xml
    
    Select
    Name = n.value('local-name(.)', 'varchar(100)'),
    Id = n.value('.', 'varchar(100)'),
    col = n.value('local-name(.)', 'varchar(100)')+' - '+n.value('.', 'varchar(100)')
    from @xml.nodes('//@*') as T(n)
    order by Name desc
    
    /*
    col
    webId - MOONCU1676747
    sourceSystem - Moon
    clientRef - UBM%2520INFORMATION%2520LTD%2520test
    brandRef - PSKO1
    */

    If you have any question, feel free to let me know.


    Eric Zhang
    TechNet Community Support


    • Edited by Eric__Zhang Thursday, April 9, 2015 9:31 AM
    • Proposed as answer by Eric__Zhang Wednesday, April 15, 2015 1:37 AM
    • Marked as answer by Eric__Zhang Monday, April 20, 2015 2:34 AM
    Thursday, April 9, 2015 9:30 AM

All replies

  • You where answered in your similar thread: https://social.msdn.microsoft.com/Forums/en-US/2f6a91cc-81cf-4096-9989-d4ef276622a8/extract-parts-of-a-string-into-columns?forum=sqlintegrationservices

    If that solution is no good, please clarify.


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

    Wednesday, April 8, 2015 3:32 PM
  • declare @test table (Col2  varchar(2000))
    Insert into @test values
    ('brandRef=PSKO1&webId=MOONCU1676747&clientRef=UBM%2520INFORMATION%2520LTD%2520test&sourceSystem=Moon')
    ,('brandRef=PSKO2&webId=MOONCU1676748&clientRef=UBM%2520INFORMATION%2520LTD%2520test2&sourceSystem=Sun')
      
      
     ;with mycte as (
       
    SELECT
    ROW_NUMBER() OVER (ORDER BY S.a.value('count(.)', 'tinyint')) rn,
     
    S.a.value('@brandRef',  'varchar(50)') as brandRef , 
    S.a.value('@webId', 'varchar(50)') as webId , 
    S.a.value('@clientRef', 'varchar(50)') as clientRef
    ,S.a.value('@sourceSystem', 'varchar(50)') as sourceSystem
      
     FROM   (
    SELECT Col2,   
    Cast(N'<H><r  ' + Replace(Replace(Col2, '&','" '),'=','="')  + '" /></H>' as XML)  AS [vals]
    FROM   @test)   d    
    CROSS APPLY d.[vals].nodes('/H/r') S(a) 
     )
      
       
      
    select * from mycte  
     

    • Proposed as answer by Eric__Zhang Wednesday, April 15, 2015 1:37 AM
    • Marked as answer by Eric__Zhang Monday, April 20, 2015 2:34 AM
    Wednesday, April 8, 2015 3:47 PM
  • SELECT MAX(CASE WHEN LEFT(f.Val,CHARINDEX('=',f.Val)-1) = 'BrandRef' THEN STUFF(f.Val,1,CHARINDEX('=',f.Val),'') END) AS BrandRef,
    MAX(CASE WHEN LEFT(f.Val,CHARINDEX('=',f.Val)-1) = 'webId' THEN STUFF(f.Val,1,CHARINDEX('=',f.Val),'') END) AS webId,
    MAX(CASE WHEN LEFT(f.Val,CHARINDEX('=',f.Val)-1) = 'clientref' THEN STUFF(f.Val,1,CHARINDEX('=',f.Val),'') END) AS clientref,
    MAX(CASE WHEN LEFT(f.Val,CHARINDEX('=',f.Val)-1) = 'sourceSystem' THEN STUFF(f.Val,1,CHARINDEX('=',f.Val),'') END) AS sourceSystem
    FROM YourTable t
    CROSS APPLY dbo.ParseValues(t.Column,&') f
    GROUP BY t.Column

    ParseValues UDF can be found here

    http://visakhm.blogspot.ae/2010/02/parsing-delimited-string.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, April 8, 2015 4:41 PM
  • Obviously there are quite a few ways to do this. Here is another one...

    DECLARE @String VARCHAR(100) = 'brandRef=PSKO1&webId=MOONCU1676747&clientRef=UBM%2520INFORMATION%2520LTD%2520test&sourceSystem=Moon'
    
    ;WITH SplitString AS (
    	SELECT 
    		REPLACE(Item, '=', '.') AS Item
    	FROM 
    		dbo.SplitCSVToTable8K(@String, '&') -- Use the string splitting function of your choice...
    ), ParseStrings AS (
    	SELECT 
    		PARSENAME(ss.Item, 2) AS Col,
    		CASE WHEN PARSENAME(ss.Item, 1) LIKE '%\%%' ESCAPE '\' THEN LEFT(PARSENAME(ss.Item, 1), PATINDEX('%[%]%', PARSENAME(ss.Item, 1))-1) ELSE PARSENAME(ss.Item, 1) END AS Val
    	FROM SplitString ss
    ) 
    	SELECT 
    		MAX(CASE WHEN ps.Col = 'brandRef' THEN ps.Val END) AS [brandRef],
    		MAX(CASE WHEN ps.Col = 'webId' THEN ps.Val END) AS [webId],
    		MAX(CASE WHEN ps.Col = 'clientRef' THEN ps.Val END) AS [clientRef],
    		MAX(CASE WHEN ps.Col = 'sourceSystem' THEN ps.Val END) AS [sourceSystem]
    	FROM ParseStrings ps

    HTH,

    Jason


    Jason Long

    • Proposed as answer by Eric__Zhang Wednesday, April 15, 2015 1:37 AM
    • Marked as answer by Eric__Zhang Monday, April 20, 2015 2:34 AM
    Wednesday, April 8, 2015 5:08 PM
  • Hello Rainbow55,

    Here's another approach, inspired by Jingyang's.

    DECLARE @xml XML
    DECLARE @Str VARCHAR(MAX)
    
    SET @Str='brandRef=PSKO1&webId=MOONCU1676747&clientRef=UBM%2520INFORMATION%2520LTD%2520test&sourceSystem=Moon'
    
    SELECT @xml= CAST(N'<H><r  ' + Replace(Replace(@Str, '&','" '),'=','="')  + '" /></H>' as XML) 
    
    SELECT @xml
    
    Select
    Name = n.value('local-name(.)', 'varchar(100)'),
    Id = n.value('.', 'varchar(100)'),
    col = n.value('local-name(.)', 'varchar(100)')+' - '+n.value('.', 'varchar(100)')
    from @xml.nodes('//@*') as T(n)
    order by Name desc
    
    /*
    col
    webId - MOONCU1676747
    sourceSystem - Moon
    clientRef - UBM%2520INFORMATION%2520LTD%2520test
    brandRef - PSKO1
    */

    If you have any question, feel free to let me know.


    Eric Zhang
    TechNet Community Support


    • Edited by Eric__Zhang Thursday, April 9, 2015 9:31 AM
    • Proposed as answer by Eric__Zhang Wednesday, April 15, 2015 1:37 AM
    • Marked as answer by Eric__Zhang Monday, April 20, 2015 2:34 AM
    Thursday, April 9, 2015 9:30 AM