Removing leading zeroes from a field in a SQL statement

select substring(ColumnName, patindex('%[^0]%',ColumnName), 10)

select replace(ltrim(replace(ColumnName,'0',' ')),' ','0')