sqlsql-serverleading-zero

replace leading zero with a character value in Sql field

I'm trying to change a field's value where the leading zeros need to be replaced with an _. Examples:

000123 should be ___123
001234 should be __1234
0012004 should be __12004.

I thought it would have been simple but can't figure it out.

Appreciate all the help i can get!

I tried a REPLACE function but that would replace all zeroes, I only want the leading zeroes replaced

Solution

  • use try_cast to make it an INT, then use STR to make it a right-aligned string of the appropriate length, then replace spaces with underscores:

    declare @mycol varchar(11)
set @mycol = '0001234'
select replace(str(try_cast(@mycol as int),len(@mycol)),' ','_');