Help needed in recursive split

Question

Post reply

Help needed in recursive split

KGJ-Dev

SSCertifiable

Points: 5780
More actions
November 4, 2016 at 10:56 am

#314618

I am using the function dbo.DelimitedSplit8K to split. i neeed to split the result of another split
Declare @UserGroup nvarchar(MAX);
SET @UserGroup = 'UserGroup1|User1|User2|User3&UserGroup2|user4|user5|user6&Usergroup3|user7|user8|user9';
selecT * FROM dbo.DelimitedSplit8K(@UserGroup,'&')
expected result
select 'UserGroup1','User1' union all
select 'UserGroup1','User2' union all
select 'UserGroup1','User3' union all
select 'UserGroup2','User4' union all
select 'UserGroup2','User5' union all
select 'UserGroup2','User6' union all
select 'UserGroup3','User7' union all
select 'UserGroup3','User8' union all
select 'UserGroup3','User9'
Any sugestion how to make the resut like i mentioned above

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Here's an option:

Declare @UserGroup nvarchar(MAX);

selecT uc.UserGroup, us.Item

FROM dbo.DelimitedSplit8K(@UserGroup,'&') gs

CROSS APPLY (SELECT LEFT( gs.Item, CHARINDEX( '|', gs.Item + '|') - 1) AS UserGroup,

SUBSTRING( gs.Item, CHARINDEX( '|', gs.Item + '|') + 1, 8000) AS Users) uc

CROSS APPLY dbo.DelimitedSplit8K( uc.Users, '|') us

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eirikur Eiriksson SSC Guru Points: 182901 More actions · Answer 2

Here is an alternative to Luis's fine solution

😎

USE TEEST;

GO

SET NOCOUNT ON;

Declare @UserGroup nvarchar(MAX);

;WITH BASE_DATA AS

(

selecT

DX01.ItemNumber AS X1

,DX02.ItemNumber AS X2

,DX02.Item AS XVAL

FROM dbo.DelimitedSplit8K(@UserGroup,CHAR(38)) DX01

CROSS APPLY dbo.DelimitedSplit8K(DX01.Item,CHAR(124)) DX02

)

,GROUPED_DATA AS

(

SELECT

MAX(CASE WHEN BD.X2 = 1 THEN BD.XVAL END) OVER

(

PARTITION BY BD.X1

) AS GROUP_NAME

,BD.XVAL AS GROUP_MEMBER

FROM BASE_DATA BD

)

SELECT

GD.GROUP_NAME

,GD.GROUP_MEMBER

FROM GROUPED_DATA GD

WHERE GD.GROUP_MEMBER <> GD.GROUP_NAME;

Output

GROUP_NAME GROUP_MEMBER

----------- -------------

UserGroup1 User1

UserGroup1 User2

UserGroup1 User3

UserGroup2 user4

UserGroup2 user5

UserGroup2 user6

Usergroup3 user7

Usergroup3 user8

Usergroup3 user9

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 3

KGJ-Dev

SSCertifiable

Points: 5780

November 4, 2016 at 11:38 am

#1910678

thanks luiz and eric. much appreciated

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

Here's another approach that just replies on DelimitedSplit8K. My gut tells me that Luis's is more efficient, but I haven't tested the different approaches against one another.

SELECT s2.Item, s3.Item

FROM dbo.DelimitedSplit8K(@UserGroup, '&') s1

CROSS APPLY dbo.DelimitedSplit8K(s1.Item, '|') s2

CROSS APPLY dbo.DelimitedSplit8K(s1.Item, '|') s3

WHERE s2.ItemNumber = 1

AND s3.ItemNumber > 1

ORDER BY s1.Item, s2.Item;

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 5

Hi Luiz, one quick question that there might be scenario where group name will not be there like below

in that case i want the group name has to be null. how do i achieve this?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

I wonder if there isn't a simpler/more efficient solution if you can introduce a third delimiter thusly (either when the string is built or with a regex, etc):

OH, I am pretty sure it is against the rules to use NVARCHAR(MAX) with DelimitedSplit8K.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 7

of course this is weired and i added another identifier to identify the group

Declare @UserGroup nvarchar(MAX);

selecT uc.UserGroup, us.Item

FROM dbo.DelimitedSplit8K(@UserGroup,'^') gs

CROSS APPLY (SELECT LEFT( gs.Item, CHARINDEX( '@', gs.Item + '@') - 1) AS UserGroup,

SUBSTRING( gs.Item, CHARINDEX( '|', gs.Item + '|') + 1, 8000) AS Users) uc

CROSS APPLY dbo.DelimitedSplit8K( uc.Users, '|') us

result:

UserGroupItem

|User1|User2|User3User1

|User1|User2|User3User2

|User1|User2|User3User3

UserGroup2user4

UserGroup2user5

UserGroup2user6

Usergroup3

instead of getting the username as |User1|User2|User3 i wanted to display null. any suggestion please

Ed Wagner SSC Guru Points: 287026 More actions · Answer 8

TheSQLGuru (11/4/2016)
I wonder if there isn't a simpler/more efficient solution if you can introduce a third delimiter thusly (either when the string is built or with a regex, etc):
SET @UserGroup = 'UserGroup1#User1|User2|User3&UserGroup2#user4|user5|user6&Usergroup3#user7|user8|user9';

Or even split out the group from the rest of the delimited list.

TheSQLGuru (11/4/2016)
OH, I am pretty sure it is against the rules to use NVARCHAR(MAX) with DelimitedSplit8K.

Nice catch, Kevin. It is most certainly against the rules. The price is performance.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 9

this is what i could think off

Declare @UserGroup nvarchar(MAX);

selecT case when charindex('|',uc.UserGroup) = 1 then null else uc.UserGroup end, us.Item

FROM dbo.DelimitedSplit8K(@UserGroup,'^') gs

CROSS APPLY (SELECT LEFT( gs.Item, CHARINDEX( '@', gs.Item + '@') - 1) AS UserGroup,

SUBSTRING( gs.Item, CHARINDEX( '|', gs.Item + '|') + 1, 8000) AS Users) uc

CROSS APPLY dbo.DelimitedSplit8K( uc.Users, '|') us

fortunately there will be 5 to 6 groups and each group will have less then 5 users. so i don't think performance will be issue here.

is there nay better solution?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

KGJ-Dev (11/4/2016)
this is what i could think off
Declare @UserGroup nvarchar(MAX);
SET @UserGroup = '|User1|User2|User3^UserGroup2@|user4|user5|user6^Usergroup3@';
selecT case when charindex('|',uc.UserGroup) = 1 then null else uc.UserGroup end, us.Item
FROM dbo.DelimitedSplit8K(@UserGroup,'^') gs
CROSS APPLY (SELECT LEFT( gs.Item, CHARINDEX( '@', gs.Item + '@') - 1) AS UserGroup,
SUBSTRING( gs.Item, CHARINDEX( '|', gs.Item + '|') + 1, 8000) AS Users) uc
CROSS APPLY dbo.DelimitedSplit8K( uc.Users, '|') us
fortunately there will be 5 to 6 groups and each group will have less then 5 users. so i don't think performance will be issue here.
is there nay better solution?

I didn't have much time to devote, but made a few quick attempts but couldn't come up with anything better than Luis C's solution, even with a third delimiter.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Luis Cazares SSC Guru Points: 183706 More actions · Answer 11

Here's a different option:

selecT uc.UserGroup, us.Item

FROM dbo.DelimitedSplit8K(@UserGroup,'^') gs

CROSS APPLY (SELECT LEFT( gs.Item, NULLIF(CHARINDEX( '@', gs.Item), 0) - 1) AS UserGroup,

SUBSTRING( gs.Item, CHARINDEX( '|', gs.Item + '|') + 1, 8000) AS Users) uc

CROSS APPLY dbo.DelimitedSplit8K( uc.Users, '|') us

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 12

KGJ-Dev

SSCertifiable

Points: 5780

November 5, 2016 at 7:55 pm

#1910784

thank you luis. this helped me.