T-SQL Remove Zero Padding as String Manipulation

Question

If you are dealing with strings as nvarchar and varchar in SQL Server what is the correct way to drop leading zeros without casting to an INT type?

Say '000123' for example. I would like to convert this into '123'

http://stackoverflow.com/questions/92093/removing-leading-zeroes-from-a-field-in-a-sql-statement This was converted to a comment because it was too short for an answer. — Dylan Brams, Dec 31 '13 at 16:57
Then why not convert it to a numeric value since that's what it is? — Zane, Dec 31 '13 at 17:15

score 3 · Accepted Answer · answered Dec 31 '13 at 17:08

DECLARE @Var VARCHAR(100) = '000000658410065446'

SELECT SUBSTRING(@Var, PATINDEX('%[^0]%',@Var), 100)

OR

SELECT SUBSTRING(@Var, PATINDEX('%[^0]%',@Var), 
                   LEN(@Var)- PATINDEX('%[^0]%',@Var)+ 1)

Both will return the same Result as follows

Result

658410065446

1 Answers1