question

Suresh.Patibandla avatar image
Suresh.Patibandla asked

Splitting a String based on the dynamic pattern

i have a string like following "N,N,N,N,N,N,N,N,R,R,R,R,R,R,R,R,R,R,R,R,R,R,R,R,N,N,N,N,N,N,N,N,R,R,R,R,R,R,R,R,R,R,R,R,R,R,R,R" This will always have 48 characters separated by Commas. Any character can be one of the letters N,R,W so now i would like to split it in such a way that the repeating occurrences would be in one row. Like the following 1) "N,N,N,N,N,N,N,N,N," 2) "W,W,W,W,W," 3) "R,R,R,R,R,R,R,R,R,R,R,R,R," 4) "N," 5) "W,W,W,W,W,W,W," 6) "R,R,R,R," 7) "W," 8) "R," 9) "N," 10) "R,R,R,R,R,R,R,R"
sql-server-2005split
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
If you only have 3 characters then I would suggest trying to adapt an already existing string splitting process (there are plenty on this forum and sqlservercentral.com) but instead of splitting on a single character - such as the comma - you should split where there is a change. So searching for 'N,R', 'N,W', 'R,N', 'R,W', 'W,N' or 'W,R' will highlight all the transitions from one character to another. It wont perform very well as you will need to pass through the data a number of times but as a one off this may facilitate what you need.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi Allen, Thanks for the help. The idea to have fixed strings really helped me to get the things done. I have successfully implemented it and delivered the functionality. However would there be any generic way to do this by avoiding the loops, do you think? Regards, Patibandla.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
Jeff Moden illustrates a few different ways to break apart strings without using loops. Absolutely explore these. [Here's the first one.][1] And [this is the second][2]. There is usually no reason to resort to loops of any kind. [1]: http://www.sqlservercentral.com/articles/T-SQL/62867/ [2]: http://www.sqlservercentral.com/articles/Tally+Table/72993/
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I'm sure there's a way to spot the change without a loop. Doesn't Itzik do something like that in his book using Islands & Gaps metaphors?
1 Like 1 ·
The reason I think multiple passes will be needed is because there are 6 different delimiters that need to be accounted for. Every option I have seen to-date only takes one delimiter at a time...
0 Likes 0 ·
Ah, that's true. To the research laboratory !
0 Likes 0 ·
Usman Butt avatar image
Usman Butt answered
Specific to your requirements, this is how I have tried to solve it. I am using Itzik style cteTally and by grouping the consecutive occurences. I have not split the string using any delimeter character but by spliiting every two characters in sequence DECLARE @pString VARCHAR(8000) SELECT @pString = 'N,N,N,N,N,N,N,N,R,R,R,R,R,R,R,R,R,R,R,R,R,R,R,R,N,N,N,N,N,N,N,N,R,R,R,R,R,R,R,R,R,R,R,R,R,R,R,R' ;WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), --10E+1 or 10 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows CTE1(N) AS ( SELECT TOP (ISNULL(DATALENGTH(@pString)/2,0) + 1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) * 2 - 1 FROM E2 ), CTE2 AS ( SELECT N, ItemValue = SUBSTRING(@pString,N,1), ItemGroup = 1 FROM CTE1 WHERE N = 1 UNION ALL SELECT C1.N,ItemValue=SUBSTRING(@pString,C1.N,1), ItemGroup = CASE WHEN C2.ItemValue = SUBSTRING(@pString,C1.N,1) THEN ItemGroup ELSE ItemGroup + 1 END FROM CTE1 C1 JOIN CTE2 C2 ON C1.N = C2.N + 2 ) SELECT ItemGroup,SUBSTRING(@pString,MIN(N),MAX(N)-MIN(N)+2) FROM CTE2 GROUP BY ItemGroup
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yes, It is really faster compared to the one that i have implemented. But i am working on sql2k5 which has some limitations to CTEs'. Thanks
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.