SQL select the last numbers from the right

user_12054675 0
Viewed 70

I am trying to select only the numbers from a string, starting from the right. So from the following examples:

1ECCA15
ECCB9

I would only like to select the numbers from the right. So the result would be:

15
9

I've tried this, but this does not select the numbers from the right.

substring(col, PatIndex('%[0-9]%', col), len(col))
sql-server-2012 sql
3 Answers

Try this:

DECLARE @t TABLE(Col VARCHAR(255));

INSERT @t SELECT '1ECCA15';
INSERT @t SELECT 'ECCB9';


SELECT REVERSE(LEFT(REVERSE(col), PATINDEX('%[a-z]%', REVERSE(col)) - 1)) FROM @t;
user_758520

There you go, make use of REVERSE

CREATE TABLE #temp(col varchar(20))
INSERT INTO #temp values('1ECCA15'),('ECCB9')

SELECT REVERSE(SUBSTRING(REVERSE(Col), 0, PATINDEX('%[^0-9]%',REVERSE(col))))
FROM #temp
user_100046210

How about this?

select right(col, patindex('%[^0-9]%',reverse(col))-1) from your_table;
user_99471590
Related