CODEWITHSUNDEEP
sqlsql-serversql-server-2008
HashCoder
HashCoder

Reputation: 946

Split string and save it to temporary table

I have a Table which is having a column with junk values 

DECLARE @tmp TABLE(junkValues nvarchar(max))

INSERT into @tmp (junkValues)
VALUES ('@{''id''= ''63FCE3C0-B6FE-4FFE-8BC8-506A338455DD''; Value="ABC"; Other="ddd"}')

INSERT into @tmp (junkValues)
VALUES ('@{''id''= ''C10560BA-7059-4AC5-B9ED-77BAC7182BD7''; Other="ddd"; Value="XYZ" }')

I wanted to convert this semicolon separated value to another temporary table.

DECLARE @outputTempTable TABLE(CDID uniqueidentifier, Value nvarchar(max))

Where CDID will be the id from @tmp and Value will be Value from @tmp

Expecting an output like below

CDID                                       Value       
-----                                      -----------
63FCE3C0-B6FE-4FFE-8BC8-506A338455DD        ABC
C10560BA-7059-4AC5-B9ED-77BAC7182BD7        XYZ

I am using SQL Server 2008, is there any way I can achieve this?

Upvotes: 0

Views: 256

Answers (3)

AK47
AK47

Reputation: 3797

A bit complex, but it Works!, please try following,

DECLARE @tmp TABLE(junkValues nvarchar(max))

INSERT into @tmp (junkValues)
VALUES ('@{''id''= ''63FCE3C0-B6FE-4FFE-8BC8-506A338455DD''; Value="ABC", Other="ddd"}')

INSERT into @tmp (junkValues)
VALUES ('@{''id''= ''C10560BA-7059-4AC5-B9ED-77BAC7182BD7''; Other="ddd"; Value="XYZ" }')

;with cte as
(
select junkValues
        ,CHARINDEX('''id''= ''',junkValues) + 7 as IDStart
        ,CHARINDEX(''';',junkValues,CHARINDEX('''id''= ''',junkValues))  as IDEnd
        ,CHARINDEX('Value="',junkValues) + 7 as valStart
        ,CHARINDEX('"',junkValues,CHARINDEX('Value="',junkValues) + 8)  as valEnd

from @tmp
)
select *
    ,SUBSTRING(junkValues,IDStart, IDEnd  - IDStart  )  as IDCol
    ,SUBSTRING(junkValues,valStart, valEnd  - valStart  )  as ValCol
    from cte

Upvotes: 2

dean
dean

Reputation: 10098

I know it's simplistic approach, but if the data is really laid out as in your sample, then this is the simplest and most efficient way:

select substring(junkValues, 10, 36) as CDID, substring(junkValues, 56, 3) as Value
from #tmp

Otherwise, one should use PATINDEX to identify start and length of substrings:

select 
substring(junkValues, patindex('%''id''= %', junkValues)+7, patindex('%; Value=%', junkValues)-11),
substring (junkValues, patindex('%Value=%', junkValues)+7, patindex('%Other=%', junkValues)-patindex('%Value=%', junkValues)-10)
from #tmp

Upvotes: 0

Aasish Kr. Sharma
Aasish Kr. Sharma

Reputation: 556

May this help you..

DECLARE @tmp TABLE(junkValues nvarchar(max))

INSERT into @tmp (junkValues)
VALUES ('@{''id''= ''63FCE3C0-B6FE-4FFE-8BC8-506A338455DD''; Value="ABC", Other="ddd"}')

INSERT into @tmp (junkValues)
VALUES ('@{''id''= ''C10560BA-7059-4AC5-B9ED-77BAC7182BD7''; Value="XYZ", Other="ddd"}')

SELECT 
SUBSTRING(junkValues,CHARINDEX('''= ''',junkValues)+4, 36) AS CDID,
SUBSTRING(junkValues,CHARINDEX('="',junkValues)+2,((CHARINDEX('", O',junkValues))-(CHARINDEX('="',junkValues)+2))) AS Value
from @tmp

Upvotes: 1

Related Questions