Parse the data and extract a value.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Parse the data and extract a value.

Author

Topic

tamancha.1
Starting Member

37 Posts

Posted - 2010-11-30 : 13:43:22

Table1:

site_data
U=ds&sv1=2922&sv2=2010&sv3=1052
U=ds&sv1=9553&sv2=2011&sv3=5849
NULL

Output
site_data sv1 sv2 sv3
U=ds&sv1=2922&sv2=2010&sv3=1052 2922 2010 1052
U=ds&sv1=9553&sv2=2011&sv3=5849 9553 2011 5849
NULL NULL NULL NULL

Please help to parse the data from Table 1 to output using SQL.Thanks.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-11-30 : 14:30:49

Brute force, ugly, but it works

Jim

declare @str varchar(100)
SET @str = --'U=ds&sv1=292211379&sv2=2010053181&sv3=105251'
'U=ds&sv1=95536814&sv2=2010053133&sv3=584910'


 

print  @str 
      + ' '      
      + substring(@str,patindex('%&sv1=%',@str) + 5  ,patindex('%&sv2=%',@str)  - (patindex('%&sv1=%',@str) +5) 
                 )
      + ' ' 
      + substring(@str,patindex('%&sv2=%',@str) + 5  ,patindex('%&sv3=%',@str)  - (patindex('%&sv2=%',@str)+5 )      
                )
      + ' '
      + substring(@str,patindex('%&sv3=%',@str) + 5  ,50       
                  )

Everyday I learn something that somebody else already knew

tamancha.1
Starting Member

37 Posts

Posted - 2010-11-30 : 15:16:05

Thanks, I am using this as in your code:

select substring (site_data,patindex('%&sv1=%',site_data) + 5 ,patindex('%&sv2=%',site_data) - (patindex('%&sv1=%',site_data) +5 )
) as sv1, substring(site_data,patindex('%&sv2=%',site_data) + 5 ,patindex('%&sv3=%',site_data) - (patindex('%&sv2=%',site_data)+5 )
) as sv2,substring(site_data,patindex('%&sv3=%',site_data) + 5 ,50
) as sv3

but it gives me error as Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.

gabling
Starting Member

6 Posts

Posted - 2010-11-30 : 16:32:24

In your site_data table, are there rows that do not contain sv2 or sv3 key/value pairs?

For example:
site_data
U=ds&sv1=2922&sv3=2010 (Missing SV2)

The query that was provided depends on having an SV2 & SV3 to correctly parse out the values. If one of these is missing, you will get the error you received.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-11-30 : 20:08:23

from

http://beyondrelational.com/blogs/madhivanan/archive/2008/09/11/splitting-delimited-data-to-columns-set-based-approach.aspx

worth looking into


CREATE TABLE #test(id int, data varchar(100))

INSERT INTO #test VALUES (1,'U=ds&sv1=2922&sv2=2010&sv3=1052')
INSERT INTO #test VALUES (2,'U=ds&sv1=9553&sv2=2011&sv3=5849') 
INSERT INTO #test VALUES (3,NULL) 

DECLARE @pivot varchar(8000)
DECLARE @select varchar(8000)



SELECT 
        @pivot=coalesce(@pivot+',','')+'[col'+cast(number+1 as varchar(10))+']'
FROM 
        master..spt_values where type='p' and 
        number<=(SELECT max(len(data)-len(replace(data,'&',''))) FROM #test)


SELECT 
    @select='
        select p.*
        from (
        select 
            id,substring(data, start+2, endPos-Start-2) as token,
            ''col''+cast(row_number() over(partition by id order by start) as varchar(10)) as n
        from (
            select 
                id, data, n as start, charindex(''&'',data,n+2) endPos
                from (select number as n from master..spt_values where type=''p'') num
                cross join 
                (
                    select 
                        id, ''&'' + data +''&'' as data 
                    from 
                        #test
                ) m
            where n < len(data)-1
            and substring(data,n+1,1) = ''&'') as data
        ) pvt
        Pivot ( max(token)for n in ('+@pivot+'))p'
PRINT @select
EXEC(@select)

DROP TABLE #test

If you don't have the passion to help people, you have no passion

gabling
Starting Member

6 Posts

Posted - 2010-11-30 : 23:21:54

Yosiasz, nice... How about a recursive CTE to strip out the SV key/value pairs? Only caveat with this is the string manipulation (+3 & +4) in the cte restricts the query to SV0-9 keys only. (Too late at night to modify my code to make this dynamic

) This uses the same #temp that was created earlier:

DECLARE @output VARCHAR(255);
WITH StringReducer (id, KeyVal, KeyNm)
AS
(SELECT id, SUBSTRING(data, PATINDEX('%sv%=%', data) + 4, LEN(data)), LEFT(SUBSTRING(data, PATINDEX('%sv%=%', data), LEN(data)),3) FROM #test
UNION ALL
SELECT id, CASE WHEN PATINDEX('%sv%=%', c.KeyVal) > 0 THEN SUBSTRING(c.KeyVal, PATINDEX('%sv%=%', c.KeyVal) + 4, LEN(c.KeyVal)) ELSE '' END,
LEFT(SUBSTRING(c.KeyVal, PATINDEX('%sv%=%', c.KeyVal), LEN(c.KeyVal)), 3)
FROM StringReducer c
WHERE LEN(c.KeyVal) > 0)
-- QUERY THE CTE AND CREATE THE RESULTSET
SELECT id, KeyNm as KeyName, CASE WHEN PATINDEX('%sv%=%', KeyVal) > 0 THEN LEFT(KeyVal, PATINDEX('%sv%=%', KeyVal)-2) ELSE KeyVal END as KeyVal
FROM StringReducer
WHERE KeyVal <> ''
ORDER BY id

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-12-01 : 10:19:49

even nicer!! but it assumes the numeric values are len = 4 in sv2 >

If you don't have the passion to help people, you have no passion

gabling
Starting Member

6 Posts

Posted - 2010-12-01 : 14:39:53

Yosiasz: Thx... The len=4 is for the SV%= string to pull that out. The numeric value (SV9=NumericValue) can be of any length, but will not recognize SV10= because it's a length of 5. Can only use it for 0-9. Would be interesting to make it dynamically calculate the length of SV%% or parse out using just the & delimiter, regardless of keyname. Another day

Glen

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-12-02 : 09:08:38

Hi
another approach using xml

declare @str table
(
guids varchar(1000)
)

insert into @str
select * from
(
values
('U=ds&sv1=2922&sv2=2010&sv3=1052'),
('U=fs&sv1=3456&sv2=2345&sv3=2345')
) as t(guids)

select * from @str

select sitedata
,i.value('@U','varchar(100)') as 'U'
,i.value('@sv1','varchar(100)') as 'sv1'
,i.value('@sv2','varchar(100)') as 'sv2'
,i.value('@sv3','varchar(100)') as 'sv3'
from
(
select [sitedata]=guids,[replaced]= cast('<New ' + Replace(REPLACE(guids,'&',''' '),'=','=''') + '''/>' as xml)
from
@str
)t
cross apply
replaced.nodes('//New')s(i)

Iam a slow walker but i never walk back

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources