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 stackoverflow.com/a/11129399/1635441 response, please refer to Arvo's answer posted: stackoverflow.com/a/662437/1635441 — ramizmoh
– ramizmoh, Commented Jan 31, 2013 at 12:43

Ian Horwill · Accepted Answer · 2008-09-18 13:07:56Z

169

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

answered Sep 18, 2008 at 13:07

Ian Horwill

3,0172 gold badges26 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Cade Roux Over a year ago

This will have problems when the string is entirely made up of "0", since it will never match a non-"0" character.

Ian Horwill Over a year ago

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 Over a year ago

@Zapnologica: No. You'd have to put it in an "update TableName set ColumnName = ..." statement.

Srivastav Over a year ago

Sure, if you use Update Statements.

OscarSosa Over a year ago

Before using this solution, please try to look into Arvo's answer posted here

|

Ben · Accepted Answer · 2013-12-31 18:14:18Z

35

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

edited Dec 31, 2013 at 18:14

Ben

53.1k36 gold badges133 silver badges156 bronze badges

answered Aug 15, 2012 at 23:51

MTZ

3833 silver badges2 bronze badges

3 Comments

brentlightsey Over a year ago

I received "Replace function requires 3 arguments." I believe it should read select replace(ltrim(replace(ColumnName,'0',' ')),' ','0')

Omaer Over a year ago

This will fail if the value has a space in it. Example: "0001 B" should become "1 B" but will become "10B" instead.

Robert Lujo Over a year ago

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

Misa Lazovic · Accepted Answer · 2016-04-18 09:10:56Z

12

You can use this:

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

edited Apr 18, 2016 at 9:10

Misa Lazovic

2,83110 gold badges34 silver badges39 bronze badges

answered Apr 18, 2016 at 8:49

Stelian

1291 silver badge4 bronze badges

1 Comment

Artemination Over a year ago

ey brother you nailed it..! a very single code that works!

AHiggins · Accepted Answer · 2015-04-14 19:24:51Z

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, 2015 at 19:24

AHiggins

7,2357 gold badges38 silver badges56 bronze badges

answered Nov 13, 2014 at 13:23

ekc

611 silver badge1 bronze badge

1 Comment

Uriah Blatherwick Over a year ago

only works for a single zero, the poster asked for plural zeroes

Taryn · Accepted Answer · 2012-10-02 11:54:04Z

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.

Ben · Accepted Answer · 2013-12-31 18:15:21Z

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

e-Fungus · Accepted Answer · 2020-03-05 23:41:07Z

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

rsenna · Accepted Answer · 2018-02-01 12:16:36Z

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

The question needs to remove the leading zeros for 000000001A, both of your answers drops the A

Vikas · Accepted Answer · 2019-01-31 06:38:57Z

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: devonblog.com/continuous-delivery/…

Bas · Accepted Answer · 2023-10-13 10:16:07Z

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

Kritner · Accepted Answer · 2015-09-02 17:04:31Z

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.

Krin · Accepted Answer · 2018-05-17 15:01:50Z

-1

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

answered May 17, 2018 at 15:01

Krin

213 bronze badges

3 Comments

Jonathan Rys Over a year ago

What does '1A' * 1 equal?

RATAN KUMAR Over a year ago

what does 10*1 will be ?

y434y Over a year ago

It was stated in the question that the column type is VARCHAR(10), therefore multiplying is not possible.

rink.attendant.6 · Accepted Answer · 2016-11-29 15:35:48Z

-3

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

edited Nov 29, 2016 at 15:35

rink.attendant.6

46.7k64 gold badges111 silver badges161 bronze badges

answered Nov 29, 2016 at 14:14

Lynn Caveny

1

1 Comment

Vivian Over a year ago

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

Madhurupa Moitra · Accepted Answer · 2018-01-10 01:40:12Z

-3

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

answered Jan 10, 2018 at 1:40

Madhurupa Moitra

13 bronze badges

1 Comment

Sunil Over a year ago

This will remove 0 irrespective of its position. Question is asking about only leading ones.

Brian Ellison · Accepted Answer · 2016-02-12 22:17:36Z

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

user3809240 · Accepted Answer · 2014-08-20 08:14:49Z

-5

select ltrim('000045', '0') from dual; LTRIM ----- 45

This should do.

answered Aug 20, 2014 at 8:14

user3809240

931 gold badge3 silver badges19 bronze badges

2 Comments

BillRob Over a year ago

There is no overload for sql server.

y434y Over a year ago

In PostgreSQL it is enought to write select trim(leading '0' from '001A');, but OP was asking for SQL Server.

Collectives™ on Stack Overflow

Removing leading zeroes from a field in a SQL statement

16 Answers 16

6 Comments

3 Comments

1 Comment

1 Comment

1 Comment

Comments

Comments

1 Comment

1 Comment

Comments

1 Comment

3 Comments

1 Comment

1 Comment

1 Comment

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

16 Answers 16

6 Comments

3 Comments

1 Comment

1 Comment

1 Comment

Comments

Comments

1 Comment

1 Comment

Comments

1 Comment

3 Comments

1 Comment

1 Comment

1 Comment

2 Comments

Linked

Related