Parse a string before the Last Index Of a character in SQL Server

Question

I started with this but is it the best way to perform the task?

select  
    reverse(
        substring(reverse(some_field),
        charindex('-', reverse(some_field)) + 1, 
        len(some_field) - charindex('-', reverse(some_field)))) 
from SomeTable

1. How does SQL Server treat the multiple calls to reverse(some_field)?
2. Besides a UDF and iterating through the string looking for charindex of the '-' and storing the last index of it, is there a more efficient way to perform this task in T-SQL?

Note that what I have works, I just am really wondering if it is the best way about it.

Below are some sample values for some_field.

s2-st, s1-st, s3-st, s3-sss-zzz, s4-sss-zzzz

EDIT:

Sample output for this would be...

s1, s2, s3-sss, s3, s4-sss

The solution Emtucifor was is actually getting the end of the string so everything after the last hyphen. I just modified his version to get everything before it instead using a similar method with the left function. Thanks for all of your your help.

select left(some_field, abs(charindex('-', reverse(some_field)) - len(some_field)))
    from (select 's2-st' as some_field
        union select 's1-st'
        union select 's3-st'
        union select 's3-sss-zzz'
        union select 's4-sss-zzzz') as SomeTable

Answer 1

Not sure about #1, but I would say that you might be better off doing this in code. Is there a reason you have to do it in the database?

Are you experiencing performance problems because of some similar code or is this purely hypothetical.

Answer 2

I am also not sure how SQL Server handles the multiple calls to REVERSE and CHARINDEX.

You can eliminate the last call to CHARINDEX since you want to take everything to the end of the string:

select  
    reverse(
        substring(reverse(some_field),
        charindex('-', reverse(some_field)) + 1, 
        len(some_field))) 
from SomeTable

Although I would recommend against it, you could also replace the LEN function call with the size of the column:

select  
    reverse(
        substring(reverse(some_field),
        charindex('-', reverse(some_field)) + 1, 
        1024)) 
from SomeTable

I am curious how much of a difference either of these changes would make.

Answer 3

May I suggest this simplification of your expression:

select right(some_field, charindex('-', reverse(some_field)) - 1)
from SomeTable

Also, there's no harm, as far as I know, in specifying 8000 characters in length with the substring function when you want the rest of the string. As long as it's not varchar(max), it works just fine.

If this is something you have to do all the time, over and over, how about #1 splitting out the data into separate columns and storing it that way, or #2 adding a calculated column with an index on it, which will perform the calculation once at update/insert time and not again later.

Last, I don't know if SQL Server is smart enough to reverse(some_field) only once and inject it into the other instance. When I get some time I'll try to figure it out.

Update

Oops, somehow I got backwards what you wanted. Sorry about that. The new expression you showed can still be simplified a little:

select left(some_field, len(some_field) - charindex('-', reverse(some_field)))
from (
   select 's2-st'
   union all select 's1-st'
   union all select 's3-st'
   union all select 's3-sss-zzz'
   union all select 's4-sss-zzzz'
   union all select 's5'
) X (some_field)

The abs() in your expression was just reversing the sign. So I put + len - charindex instead of + charindex - len and all is well now. It even works for strings without dashes.

One more thing to mention: your UNION SELECTs should be UNION ALL SELECT because without the all, the engine has to remove duplicates like you'd said SELECT DISTINCT. Just get in the habit of using ALL and you'll be good. :)

Answer 4

1. The 3 inner reverses are discrete from each other. The outer reverse will reverse anything that is already reversed by the inner ones.

2. Emtucifor's approach is best as a pure TSQL solution. You don't need LEN