Best way to parse string

Transact-SQL
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.