SQLTeam.com | Weblogs | Forums

Best way to parse string

sql2008

#1

Here's the string: Scene1_Slide84_MultiResponse_0_0

I need to get the Slide84 and MultiResponse separately.

What's the best TSQL to do this? (Using SQL 2008)

Sam


#2

One way:

    select SUBSTRING(string, _1st.underscore+1, _2nd.underscore-_1st.underscore-1) as firstString
    , SUBSTRING(string, _2nd.underscore+1, _3rd.underscore-_2nd.underscore-1) as firstString
    , _1st.underscore, _2nd.underscore, _3rd.underscore, _4th.underscore
from (values ('Scene1_Slide84_MultiResponse_0_0'))v(string)
cross apply (select CHARINDEX('_', string)) _1st(underscore)
cross apply (select CHARINDEX('_', string, _1st.underscore+1)) _2nd(underscore)
cross apply (select CHARINDEX('_', string, _2nd.underscore+1)) _3rd(underscore)
cross apply (select CHARINDEX('_', string, _3rd.underscore+1)) _4th(underscore)

#3

Another way:

declare @s varchar(50) = 'Scene1_Slide84_MultiResponse_0_0';

select 
  LEFT(SUBSTRING(@s, CHARINDEX('_', @s)+1, 50), CHARINDEX('_', SUBSTRING(@s, CHARINDEX('_', @s)+1, 50))-1),
  LEFT(SUBSTRING(SUBSTRING(@s, CHARINDEX('_', @s)+1, 50), CHARINDEX('_', SUBSTRING(@s, CHARINDEX('_', @s)+1, 50))+1, 50), CHARINDEX('_', SUBSTRING(SUBSTRING(@s, CHARINDEX('_', @s)+1, 50), CHARINDEX('_', SUBSTRING(@s, CHARINDEX('_', @s)+1, 50))+1, 50))-1);

#4

I had hoped SQL 2008 would have brought something simpler like a RegEx to bear.

Thank you both for your solutions. I've got it working.

Sam


#5

If you Google for "SQL Server Regex CLR" you'll find some code you can use to do regex matching.


#6

Native Regex is one of the things that I would most love to see in a future version of SQL Server. From whatever little I have heard of SQL 2016, it doesn't seem like it is going to be available even then.


#7

My guess is that its not hard to do so performance might be the issue?
Long time since I used a CLR for RegEx, but it was the most unstable thing on the server :frowning:


#8

I've never played with CLR, it seemed the learning overhead was too high compared to using CHARINDX.


#9

And the slowest! I couldn't believe how slow it actually was compared to doing the same thing with LIKE or PATINDEX when possible.


#10

My recommendation would be to go get the DelimitedSplit8K function at the following article, split it on the underscores, and then select/pivot whatever you want.
Tally OH! An Improved SQL 8K “CSV Splitter” Function


#11

My recommendation would be to go get the DelimitedSplit8K function at
the following article, split it on the underscores, and then
select/pivot whatever you want.

Yes, that is indeed the best general solution, but overkill for this simple problem I think.