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.

As a upvote to David Walker's http://stackoverflow.com/a/11129399/1635441 response, please refer to Arvo's answer posted: http://stackoverflow.com/a/662437/1635441 — ramizmoh, Jan 31 '13 at 12:43

score 167 · Accepted Answer · answered Sep 18 '08 at 13:07

167

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

answered Sep 18 '08 at 13:07

Ian Horwill

2,957
2
24
24

9

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 '09 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 '09 at 09:26
@Zapnologica: No. You'd have to put it in an "update TableName set ColumnName = ..." statement. – Ian Horwill Sep 19 '14 at 15:41
Sure, if you use Update Statements. – Srivastav Dec 22 '14 at 10:11
3

Before using this solution, please try to look into Arvo's answer [posted here](https://stackoverflow.com/questions/662383/better-techniques-for-trimming-leading-zeros-in-sql-server) – OscarSosa Jan 17 '19 at 14:31
Here's a better link to the answer mentioned by OscarSosa: https://stackoverflow.com/questions/662383/better-techniques-for-trimming-leading-zeros-in-sql-server#answer-662437 – derekbaker783 Apr 05 '22 at 15:25

score 32 · Answer 2 · edited Dec 31 '13 at 18:14

32

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

edited Dec 31 '13 at 18:14

Ben

51,770
36
127
149

answered Aug 15 '12 at 23:51

MTZ

353
3
2

I received "Replace function requires 3 arguments." I believe it should read select replace(ltrim(replace(ColumnName,'0',' ')),' ','0') – brentlightsey Jan 10 '13 at 14:46
6

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 '14 at 17:41
2

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 '16 at 12:23

score 9 · Answer 3 · edited Apr 18 '16 at 09:10

9

You can use this:

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

edited Apr 18 '16 at 09:10

Misa Lazovic

2,805
10
32
38

answered Apr 18 '16 at 08:49

Stelian

99
1
3

ey brother you nailed it..! a very single code that works! – Artemination Aug 10 '23 at 12:57

score 6 · Answer 4 · edited Apr 14 '15 at 19:24

6

I had the same need and used this:

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

edited Apr 14 '15 at 19:24

AHiggins

7,029
6
36
54

answered Nov 13 '14 at 13:23

ekc

61
1
1

2

only works for a single zero, the poster asked for plural zeroes – Uriah Blatherwick Nov 18 '20 at 21:53

score 5 · Answer 5 · edited Oct 02 '12 at 11:54

5

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.

edited Oct 02 '12 at 11:54

Taryn

242,637
56
362
405

answered Aug 17 '12 at 19:58

Nat

51
1
1

7

How is it a leading 0 if something comes before it? – xxbbcc Jul 11 '14 at 16:02

score 3 · Answer 6 · edited Dec 31 '13 at 18:15

3

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

edited Dec 31 '13 at 18:15

Ben

51,770
36
127
149

answered Dec 21 '10 at 23:25

Kathryn Wilson

31
1

score 1 · Answer 7 · answered Mar 05 '20 at 23:41

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

score 0 · Answer 8 · edited Feb 01 '18 at 12:16

0

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))

edited Feb 01 '18 at 12:16

rsenna

11,775
1
54
60

answered Feb 01 '18 at 06:10

Shailendra Mishra

71
10

The question needs to remove the leading zeros for 000000001A, both of your answers drops the A – e-Fungus Jun 07 '23 at 23:39

score 0 · Answer 9 · answered Jan 31 '19 at 06:38

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

I'd try to avoid RBAR, personally: https://www.devonblog.com/continuous-delivery/unlock-true-potential-of-sql-server-by-avoiding-rbar-programming/ — derekbaker783, Apr 05 '22 at 15:21

score -1 · Answer 10 · edited Sep 02 '15 at 17:04

-1

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.

edited Sep 02 '15 at 17:04

Kritner

13,557
10
46
72

answered Apr 08 '14 at 13:55

Afzal

9

3

How exactly does this answer the question? – deutschZuid Aug 18 '14 at 04:23

score -1 · Answer 11 · answered May 17 '18 at 15:01

-1

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

answered May 17 '18 at 15:01

Krin

11
2

3

What does '1A' * 1 equal? – Jonathan Rys May 17 '18 at 15:29
what does 10*1 will be ? – RATAN KUMAR Sep 06 '19 at 18:45
It was stated in the question that the column type is VARCHAR(10), therefore multiplying is not possible. – y434y Aug 07 '20 at 08:36

score -3 · Answer 12 · edited Nov 29 '16 at 15:35

-3

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

edited Nov 29 '16 at 15:35

rink.attendant.6

44,500
61
101
156

answered Nov 29 '16 at 14:14

Lynn Caveny

1

1

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 '16 at 14:31

score -3 · Answer 13 · answered Jan 10 '18 at 01:40

-3

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

answered Jan 10 '18 at 01:40

Madhurupa Moitra

15
3

4

This will remove 0 irrespective of its position. Question is asking about only leading ones. – Sunil Jan 10 '18 at 02:03

score -4 · Answer 14 · answered Feb 12 '16 at 22:17

-4

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

answered Feb 12 '16 at 22:17

Brian Ellison

1

It's also not dynamic to allow for more than 3 zeroes. – JoeFletch May 31 '17 at 17:43

score -5 · Answer 15 · answered Aug 20 '14 at 08:14

-5

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

LTRIM
-----
45

This should do.

answered Aug 20 '14 at 08:14

user3809240

93
1
3
18

2

There is no overload for sql server. – BillRob Dec 01 '14 at 17:52
In PostgreSQL it is enought to write `select trim(leading '0' from '001A');`, but OP was asking for SQL Server. – y434y Aug 07 '20 at 08:44

Removing leading zeroes from a field in a SQL statement

15 Answers15

Linked

Related