Removing leading zeroes from a field in a SQL statement

Question

I am working on a SQL query that reads from a SQLServer database to produce an extract file. One of the requirements to remove the leading zeroes from a particular field, which is a simple VARCHAR(10) field. So, for example, if the field contains '00001A', the SELECT statement needs to return the data as '1A'.

Is there a way in SQL to easily remove the leading zeroes in this way? I know there is an RTRIM function, but this seems only to remove spaces.

Answer 1

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

Answer 2

Will your data always need to be stripped of the leading zeros? If so it would be more efficient interms of performance to fix the data in the table and when it is entered or imported than to run a function in every select.