hi All,
I have a string varaible passed to the SP something like :
@.var = 'v1#1@.v2#1,2@.v3#1,3,4,5'. Now i have to extract the data from this variable in such a way that :
select * from var_data shud return like this :
ID Role
v1 1
v2 1
v2 2
v3 1
v3 3
v3 4
v3 5
Plz guide me how to achieve this result from the variable.
Thanks in advance :-)
This looks like a pretty good match for Jens Suessmeyer's split function with a CROSS APPLY join. Hang on and I'll get you an example.
Code Snippet
declare @.var varchar(40)
set @.var = 'v1#1@.v2#1,2@.v3#1,3,4,5'
select @.var
select left(a.splitValue, charindex('#', a.splitValue) - 1) as [ID],
b.splitValue as [Role]
from split(@.var, '@.') a
cross apply split(substring(splitValue, charindex('#', splitValue)+1, 99), ',') b
/*
ID Role
-- -
v1 1
v2 1
v2 2
v3 1
v3 3
v3 4
v3 5
*/
MVP Jens Suessmeyer's SPLIT function can be found at this post:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17
