SQL Server - select substring of all characters fo

Question

I am working with a database of products, 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 issue is that the number of hyphens, product ID, and color code can all be different.

Here are four examples:

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

The color codes in this case would be 001, 0165, M2235, and 001. What would be the best way to select these into their own column?

Answer 1

It is great to check whether the hyphen exists or not in the string, to avoid the following error:

Invalid length parameter passed to the right function.

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

Answer 2

I think the following does what you want:

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

In the event that you might have no hyphens in the value, then use a case:

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