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

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

Answer 3

You can use this:

SELECT REPLACE(LTRIM(REPLACE('000010A', '0', ' ')),' ', '0')