CODEWITHSUNDEEP
sqlsql-servert-sql
Tim C
Tim C

Reputation: 70648

Removing leading zeroes from a field in a SQL statement

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.

Upvotes: 112

Views: 387096

Answers (16)

Bas
Bas

Reputation: 35

On SQL Server 2017 (14.x), TRIM supports removing characters other than spaces using TRIM ( [ characters FROM ] string )

Starting with SQL Server version 2022 (16.x), the TRIM function supports the additional LEADING|TRAILING|BOTH options TRIM ( [ LEADING | TRAILING | BOTH ] [characters FROM ] string )

declare @string nvarchar(max)   =  '00001A';
select @string as [string]
     , trim(leading '0' from @string) as [trim (leading '0' from @string)]    --- Needs SQL Server 2022 (16.x) or later
     , trim(        '0' from @string) as [trim ('0' from @string)]            --- Needs SQL Server 2017 (14.x) or later

Keep in mind that prior to the 2022 version, TRIM will remove specified character(s) from both sides of the string, so TRIM ('0' from '0000A1000') will result in A1

Source: learn.microsoft.com

Upvotes: 0

e-Fungus
e-Fungus

Reputation: 343

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
                   ,1
                   ,patindex('%[^0]%',ColumnName)-1
                   ,'')
             ,REPLACE(ColumnName,'0','')
             )

See in fiddler various scenarios it will cover

https://dbfiddle.uk/?rdbms=sqlserver_2012&fiddle=14c2dca84aa28f2a7a1fac59c9412d48

Upvotes: 1

Vikas
Vikas

Reputation: 24332

Here is the SQL scalar value function that removes leading zeros from string:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 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)
AS
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)
    END

    -- Return the result of the function
    RETURN @Result

END
GO

Upvotes: 0

Krin
Krin

Reputation: 21

To remove leading 0, You can multiply number column with 1 Eg: Select (ColumnName * 1)

Upvotes: -1

Shailendra Mishra
Shailendra Mishra

Reputation: 71

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
      ELSE 
           CAST(CAST(@LeadingZeros AS INT) AS VARCHAR(10)) 
      END   

SELECT @LeadingZeros

Or you can simply call

CAST(CAST(@LeadingZeros AS INT) AS VARCHAR(10))

Upvotes: 0

Madhurupa Moitra
Madhurupa Moitra

Reputation: 15

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

Upvotes: -3

Lynn Caveny
Lynn Caveny

Reputation: 1

select CASE
         WHEN TRY_CONVERT(bigint,Mtrl_Nbr) = 0
           THEN ''
           ELSE substring(Mtrl_Nbr, patindex('%[^0]%',Mtrl_Nbr), 18)
       END

Upvotes: -3

Stelian
Stelian

Reputation: 129

You can use this:

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

Upvotes: 12

Brian Ellison
Brian Ellison

Reputation: 1

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

Upvotes: -4

Afzal
Afzal

Reputation: 9

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.

Upvotes: -1

ekc
ekc

Reputation: 61

I had the same need and used this: 

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

Upvotes: 6

user3809240
user3809240

Reputation: 93

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

LTRIM
-----
45

This should do.

Upvotes: -5

Kathryn Wilson
Kathryn Wilson

Reputation: 31

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

Upvotes: 3

MTZ
MTZ

Reputation: 383

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

Upvotes: 35

Nat
Nat

Reputation: 51

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.

Upvotes: 5

Ian Horwill
Ian Horwill

Reputation: 3025

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

Upvotes: 169

Related Questions