15

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.

user2525015
  • 271
  • 2
  • 5
  • 13

2 Answers2

33

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.

Hart CO
  • 34,064
  • 6
  • 48
  • 63
  • I am using SQL Server. I just edited the subject to include that. Thanks for the quick response. – user2525015 Sep 25 '13 at 13:56
  • I get the message "invalid length parameter" with the above. It is interpreting the -1 as a length. The query works if I substitute a positive number. Can I get the above to work for an unspecified length? – user2525015 Sep 25 '13 at 14:33
  • That happens when there is no hyphen, the second query is built for that. – Hart CO Sep 25 '13 at 14:36
  • I am getting the correct result set despite the error. I don't get the error with a positive number. It seems to work, but I don't know why it causes an error. – user2525015 Sep 25 '13 at 14:49
  • Yes, the second query does not cause the error. I should have figured that out. Thanks again. – user2525015 Sep 25 '13 at 14:52
2

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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Thanks. What do you mean by definition of the column? Do you mean the type? – user2525015 Sep 25 '13 at 14:29
  • @user2525015 I mean the length. VARCHAR(255), VARCHAR(8000), etc. – Aaron Bertrand Sep 25 '13 at 14:30
  • That causes the error "varchar is not a built in function name". I'm using SQL Server 2008. I initially neglected to include that. I don't know if that has anything to do with the error. – user2525015 Sep 25 '13 at 15:23
  • @user2525015 Sorry, I have no idea what you're talking about. Look at the design of the table. Column2 will be varchar or nvarchar, with a given length. Use that length. The code in my answer doesn't contain the word varchar so I have no idea what you're running that's generating that error. – Aaron Bertrand Sep 25 '13 at 15:25
  • Select SUBSTRING(column2, COALESCE(NULLIF(CHARINDEX('-',column2)+1,1),1,50)) causes the error "substring function requires 3 arguments". That is the message I should have posted. I did throw varchar in there after that on the slight chance that is what you meant. – user2525015 Sep 25 '13 at 15:41
  • It works now. I closed it at the wrong place. I don't think I can choose 2 posts as answers. – user2525015 Sep 25 '13 at 16:02