CODEWITHSUNDEEP
sqlsql-server-2012
LucasC922
LucasC922

Reputation: 169

SQL select the last numbers from the right

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

Upvotes: 2

Views: 96

Answers (3)

Rajat
Rajat

Reputation: 5793

How about this?

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

Upvotes: 1

A Redfearn
A Redfearn

Reputation: 528

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

Upvotes: 1

squillman
squillman

Reputation: 13641

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;

Upvotes: 1

Related Questions