Gregory Brauninger Gregory Brauninger - 1 month ago 7
SQL Question

Remove Leading Zeros Except last Zero

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

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