Coder Perfect

In a SQL statement, removing leading zeroes from a field

Category: sql

Tag: , ,

Problem

I’m working on a SQL query that generates an extract file from a SQLServer database. One of the requirements for removing the leading zeroes from a simple VARCHAR(10) field is to remove the leading zeroes. If the field includes ‘00001A,’ for example, the SELECT statement must return the data as ‘1A.’

Is there a way to eliminate the leading zeroes in SQL in this manner? I’m aware of the RTRIM function, but it simply appears to remove spaces.

Asked by Tim C

Solution #1

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

Answered by Ian Horwill

Solution #2

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

Answered by MTZ

Solution #3

select substring(substring('B10000N0Z', patindex('%[0]%','B10000N0Z'), 20), 
    patindex('%[^0]%',substring('B10000N0Z', patindex('%[0]%','B10000N0Z'), 
    20)), 20)

returns N0Z, which means it will remove all leading zeroes and anything preceding them.

Answered by Nat

Solution #4

I had the similar requirement and used the following:

select 
    case 
        when left(column,1) = '0' 
        then right(column, (len(column)-1)) 
        else column 
      end

Answered by ekc

Solution #5

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

Answered by Kathryn Wilson

Post is based on https://stackoverflow.com/questions/92093/removing-leading-zeroes-from-a-field-in-a-sql-statement