Better techniques for trimming leading zeros in SQL Server?
SUBSTRING(str_col, PATINDEX('%[^0]%', str_col+'.'), LEN(str_col))
Why don't you just cast the value to INTEGER and then back to VARCHAR?
SELECT CAST(CAST('000000000' AS INTEGER) AS VARCHAR)
--------
0