sql server - Removing leading zeroes from a field in a SQL statement

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

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.

As a upvote to David Walker's stackoverflow.com/a/11129399/1635441 response, please refer to Arvo's answer posted: stackoverflow.com/a/662437/1635441 – ramizmoh Jan 31, 2013 at 12:43 This will have problems when the string is entirely made up of "0", since it will never match a non-"0" character. – Cade Roux Mar 19, 2009 at 14:09 True. It will return all the unmodified string of zeros in this case. If this is a problem, the return value of patindex will have to be tested against zero. – Ian Horwill Apr 20, 2009 at 9:26 @Zapnologica: No. You'd have to put it in an "update TableName set ColumnName = ..." statement. – Ian Horwill Sep 19, 2014 at 15:41 I received "Replace function requires 3 arguments." I believe it should read select replace(ltrim(replace(ColumnName,'0',' ')),' ','0') – brentlightsey Jan 10, 2013 at 14:46 This will fail if the value has a space in it. Example: "0001 B" should become "1 B" but will become "10B" instead. – Omaer May 27, 2014 at 17:41 Like @Omaer mentioned, this is not safe if there are some spaces in string. Improved solution - first replace spaces with char that is unlikely to get in input, and after 0-ltrim, restore back these chars to spaces after. In the end looks quite complex :( Example: select replace(replace(ltrim(replace(replace('000309933200,00 USD', ' ', '|'),'0',' ')),' ','0'), '|', ' ') --> 309933200,00 USD – Robert Lujo Jun 16, 2016 at 12:23

select substring(substring('B10000N0Z', patindex('%[0]%','B10000N0Z'), 20), 
    patindex('%[^0]%',substring('B10000N0Z', patindex('%[0]%','B10000N0Z'), 
    20)), 20)
returns N0Z, that is, will get rid of leading zeroes and anything that comes before them.
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) 
In case you want to remove the leading zeros from a string with a unknown size.
You may consider using the STUFF command. 
Here is an example of how it would work.
SELECT ISNULL(STUFF(ColumnName
                   ,patindex('%[^0]%',ColumnName)-1
             ,REPLACE(ColumnName,'0','')
See in fiddler various scenarios it will cover
https://dbfiddle.uk/?rdbms=sqlserver_2012&fiddle=14c2dca84aa28f2a7a1fac59c9412d48
You can try this - it takes special care to only remove leading zeroes if needed:
DECLARE @LeadingZeros    VARCHAR(10) ='-000987000'
SET @LeadingZeros =
      CASE WHEN PATINDEX('%-0', @LeadingZeros) = 1   THEN 
           @LeadingZeros
           CAST(CAST(@LeadingZeros AS INT) AS VARCHAR(10)) 
SELECT @LeadingZeros
Or you can simply call
CAST(CAST(@LeadingZeros AS INT) AS VARCHAR(10)) 
Here is the SQL scalar value function that removes leading zeros from string:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
-- =============================================
-- Author:      Vikas Patel
-- Create date: 01/31/2019
-- Description: Remove leading zeros from string
-- =============================================
CREATE FUNCTION dbo.funRemoveLeadingZeros 
    -- Add the parameters for the function here
    @Input varchar(max)
RETURNS varchar(max)
BEGIN
    -- Declare the return variable here
    DECLARE @Result varchar(max)
    -- Add the T-SQL statements to compute the return value here
    SET @Result = @Input
    WHILE LEFT(@Result, 1) = '0'
    BEGIN
        SET @Result = SUBSTRING(@Result, 2, LEN(@Result) - 1)
    -- Return the result of the function
    RETURN @Result
To remove the leading 0 from month following statement will definitely work.
SELECT replace(left(Convert(nvarchar,GETDATE(),101),2),'0','')+RIGHT(Convert(nvarchar,GETDATE(),101),8) 
Just Replace GETDATE() with the date field of your Table.
                It was stated in the question that the column type is VARCHAR(10), therefore multiplying is not possible.
– y434y
                Aug 7, 2020 at 8:36
         WHEN TRY_CONVERT(bigint,Mtrl_Nbr) = 0
           THEN ''
           ELSE substring(Mtrl_Nbr, patindex('%[^0]%',Mtrl_Nbr), 18)
                You should give explanation with your answers, not just a single code snippet. Also, please format code as code (there's a button along the top of the text-entry box).
– Vivian
                Nov 29, 2016 at 14:31
                This will remove 0 irrespective of its position. Question is asking about only leading ones.
– Sunil
                Jan 10, 2018 at 2:03
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))
                In PostgreSQL it is enought to write select trim(leading '0' from '001A');, but OP was asking for SQL Server.
– y434y
                Aug 7, 2020 at 8:44