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开发者_StackOverflow.

ex

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

Thanks for all your help

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

declare @value varchar(20) = 'NA000000123456'

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

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