last delimter in string

[Out2work]

I have a table products and a coloumn called description

data = 'test1-test2-test3-colour'

all i want is to get the data from the last -(dash)

 

[Out2work]

Solution found

SELECT RIGHT(ltrim(rtrim(description)), CHARINDEX('-', REVERSE(ltrim(rtrim(description)))) - 1)
from products

 

[Emtucifor]

Your LTrims are doing no good because you aren't doing anything with that end of the string. So if you're expecting the last token to have no spaces surrounding it, you need to change your expression. (Try 'test1- color' with your expression and see what happens.)

SELECT LTrim(RTrim(Right(description, CharIndex('-', Reverse(description)) - 1)))

 

[drlex]

Or in SQL server 2005:

SELECT  Ltrim(Rtrim(parsename(replace(description,'-','.'),1)))

I suspect it may be slower to evaluate PARSENAME & REPLACE than REVERSE, RIGHT and CHARINDEX.

soi la, soi carré

 

[Emtucifor]

I prefer to avoid parsename because it performs so badly. Plus, we don't know that it will always be the 4th token--the rules could change. Parsename won't go to a 5th token.

 

[JeffModen]

What do you mean that it "performs so badly"? Performance wise?

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[Emtucifor]

Hmmm I'm not sure why I had that impression that it performs badly. I guess I was basing that on previous peeks into system stored procedures that people use for shortcuts, which in fact are not well written. But this is a function and I have no idea how it's implemented internally. I'd be interested in some performance tests between the 2 methods given above, but I don't have time to do it right now.