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

Question

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')))

are you sure this is mysql? mysql does not have a LEN() function. if you meant LENGTH, mixing LENGTH and SUBSTRING doesn't work well with non-ascii data; you should use CHAR_LENGTH instead — ysth, Sep 23 '22 at 22:56

score 1 · Answer 1 · answered Sep 23 '22 at 21:44

1

Oracle does not support the functions:

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

answered Sep 23 '22 at 21:44

MT0

Thank you. I actually made a mistake, this is mysql, was doing oracle sql earlier in the day. – Joseph Lariviere Sep 23 '22 at 21:48

score 0 · Answer 2 · answered Sep 23 '22 at 21:53

0

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

answered Sep 23 '22 at 21:53

nbk

2 Answers2