CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2000
Wil P
Wil P

Reputation: 3371

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

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 ErikE wrote 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

Upvotes: 3

Views: 10507

Answers (4)

gbn
gbn

Reputation: 432271

  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. ErikE's approach is best as a pure TSQL solution. You don't need LEN

Upvotes: 0

ErikE
ErikE

Reputation: 50241

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 just as if you'd indicated SELECT DISTINCT. Simply get in the habit of using ALL and you'll be much better off. :)

Upvotes: 5

Jeff Ogata
Jeff Ogata

Reputation: 57783

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.

Upvotes: 0

Abe Miessler
Abe Miessler

Reputation: 85056

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.

Upvotes: 0

Related Questions