SUBSTRING(str_col, PATINDEX('%[^0]%', str_col+'.'), LEN(str_col))

http://stackoverflow.com/questions/662383/better-techniques-for-trimming-leading-zeros-in-sql-server

http://stackoverflow.com/questions/92093/removing-leading-zeroes-from-a-field-in-a-sql-statement