SQL - Return all characters at the end of a string after a "-" at the end

Joseph Lariviere 0
Viewed 20

For example 'ABC-12345-6789-10' I want to return "10" as these are all the characters after the last "-" dash.

I have tried this but I only get a zero at the end:

SUBSTRING('ABC-12345-6789-10',len('ABC-12345-6789-10'),LEN(CHARINDEX('-', 'ABC-12345-6789-10')))
mysql sql
2 Answers

Oracle does not support the functions:

  • SUBSTRING - you want SUBSTR instead.
  • LEN - you want LENGTH instead.
  • CHARINDEX - you want INSTR instead.

You want:

SELECT value,
       SUBSTR(value, INSTR(value, '-', -1) + 1) AS last_term
FROM   (SELECT 'ABC-12345-6789-10' AS value FROM DUAL)

Which outputs:

VALUE LAST_TERM
ABC-12345-6789-10 10

fiddle

MT00

For MySQL it is easier. there you have SUBSTRING_INDEX

SELECT SUBSTRING_INDEX('ABC-12345-6789-10','-' ,-1)
SUBSTRING_INDEX('ABC-12345-6789-10','-' ,-1)
10

fiddle

nbk0
Related