sql - Extract characters to the right of a delimited value in a SELECT statement

Question

I need to extract all the characters to the right of a hyphen as part of a select statement. There will be other columns in the select. In the below query, the right three characters are selected from the second column. How would I extract an indefinite number of characters to the right of a delimiter – in my case a hyphen? Can I use the right function? Do I need to use another function?

Select column1, right(column2,3) as extracted, column3
From myTable

I am using SQL Server 2008.

Answer 1

This question has a database specific answer.

If using SQL Server:

SELECT column1
     , RIGHT(column2,CHARINDEX('-',REVERSE(column2))-1) as extracted
     , column3 
FROM myTable

You can add a CASE statement or use NULLIF() in case the hyphen isn't always present:

SELECT column1
     , CASE WHEN column2 LIKE '%-%' THEN RIGHT(column2,CHARINDEX('-',REVERSE(column2))-1) 
           END as extracted
     , column3 
FROM myTable

Or:

SELECT column1
     , RIGHT(column2,NULLIF(CHARINDEX('-',REVERSE(column2)),0)-1) as extracted
     , column3 
FROM myTable

If using MySQL just change CHARINDEX() to LOCATE(). I believe Oracle it's INSTR() and the first two parameters are switched, first it's the string you're searching in, then the string you're searching for.