Remove Leading Zeros Except last Zero

Question

Database - SQL Server 2012

I am currently using the following code:

substring(
    SUBSTRING(col001, 59, 8), 
    patindex(
        '%[^0]%', 
        SUBSTRING(col001, 59, 8)
    ),
    10
) as TOTAL_DETAIL_RECORD_COUNT

A lot of substrings, I know but it is working for the most part. There is one catch though. Some of the column values are 000000000. When this is the case, the substring/patindex clause just leaves it as is. Is there something I can do to turn a value of 000000000 to return 0. Just one zero? The length of leading zeros may not always be the same.

Thanks, Greg

Answer 1

Cast your substring as in INT and then convert it back to a string

Select cast(cast('000000000' as int) as varchar(25))  -- Returns a string of 0
Select cast(cast('000000025' as int) as varchar(25))  -- Returns a string of 25