Clever Geek Handbook

SQL Server - select substring of all characters after last hyphen

I am working with a product database trying to extract the product color from a combined ID / color code column where the color code is always the string following the last hyphen in the column. The problem is that the number of hyphens, product ID and color code may be different.

Here are four examples:

ABC123-001
BCD45678-0165
S-XYZ999-M2235
A-S-ABC123-001

The color in this case, the codes will be 001

, 0165

, M2235

and 001

. What would be the best way to select them in your column?

+3


source to share


2 answers


I think the following does what you want:

select right(col, charindex('-', reverse(col)) - 1)


If you don't have a hyphen in your meaning, use case

:

select (case when col like '%-%'
             then right(col, charindex('-', reverse(col)) - 1)
             else col
        end)
+14


source


It's good to check if a hyphen exists or not in a string to avoid the following error:

Invalid length parameter passed to required function.



SELECT CASE WHEN Col like '%\%' THEN RIGHT(Col,CHARINDEX('\',REVERSE(Col))-1) ELSE '' END AS ColName
0


source






More articles:


All Articles