CODEWITHSUNDEEP
sqlsql-servert-sql
Bill
Bill

Reputation: 33

selecting digits right of the last zero in SQL Server

I have a table with a number NA0000000012345. I'd like to select 12345, and then when it is NA0000000123456 I'd like the select statement to correct for it. So i need a condition in the RIGHT statement essentially and I'm not sure how to structure this in SQL Server.

ex 

Right(Mystring,left of last 0 starting from the left)

Thanks for all your help

Upvotes: 3

Views: 151

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44326

In case you have a mix of letters and number and only want the last number, here is the syntax. The script starts from the right side of the 'value' and search for the first none number.

declare @t table(value varchar(20)) 

insert @t values ('NA000000123a456')
insert @t values ('NA0100000123456')
insert @t values ('NA0100a00123456')

SELECT CAST(RIGHT(value, patindex('%[^0-9]%', reverse(value)) -1) as BIGINT)
FROM @t

Result:

456
100000123456
123456

Upvotes: 0

Alex K.
Alex K.

Reputation: 175846

You can grab from the 1st non-zero numeric character;

declare @value varchar(20) = 'NA000000123456'

select substring(@value, patindex('%[1-9]%', @value), len(@value))

Upvotes: 5

Related Questions