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

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.

  • LINQ to SQL - Mapping Stored Procedure with Multiple Results and using in webservice
  • A better way to write a Case statement in DATEDIFF()?
  • Run stored procedure with declared variable ms access
  • SQL Server query for name value pairs
  • SQL Where Clause To Match Against Both Conditions Simultaneously
  • How to select only the first rows for each unique value of a column
  • 2 Solutions collect form web for “Extract characters to the right of a delimited value in a SELECT statement”

    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.

    How about:

    SUBSTRING(column2, CHARINDEX('-',column2)+1, 8000)
    

    (Replace 8000 with the definition of the column.)

    Of course if the column might not always contain a hyphen, then you can say:

    SUBSTRING(column2, COALESCE(NULLIF(CHARINDEX('-',column2)+1,1),1),8000)
    

    If the column does not contain a hyphen, you’ll get the whole column. If you want to exclude those from the result, you can add a WHERE clause:

    WHERE CHARINDEX('-', column2) > 0
    

    Or you can use a CASE expression like Goat CO’s answer.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.