Removing leading zeroes from a field in a SQL stat

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

You can use this:

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

Answer 2

you can try this SELECT REPLACE(columnname,'0','') FROM table

Answer 3

select ltrim('000045', '0') from dual;

LTRIM
-----
45

This should do.

Answer 4

If you want the query to return a 0 instead of a string of zeroes or any other value for that matter you can turn this into a case statement like this:

select CASE
      WHEN ColumnName = substring(ColumnName, patindex('%[^0]%',ColumnName), 10) 
       THEN '0'
      ELSE substring(ColumnName, patindex('%[^0]%',ColumnName), 10) 
      END

Answer 5

I borrowed from ideas above. This is neither fast nor elegant. but it is accurate.

CASE

WHEN left(column, 3) = '000' THEN right(column, (len(column)-3))

WHEN left(column, 2) = '00' THEN right(a.column, (len(column)-2))

WHEN left(column, 1) = '0' THEN right(a.column, (len(column)-1))

ELSE 

END

Answer 6

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