T-SQL remove leading zeroes in a string

Question

I have string like this:

00876H873 - I want 876H873

00876 876500 - I want 876 876500

0000HUJ8 9IU877 8UJH - I want HUJ8 9IU877 8UJH

I use:

SELECT REPLACE(LTRIM(REPLACE(ColumnName, '0', ' ')),' ', '0')

This works in the first and second cases, i.e. if there is no space or exactly one space; however, if there are multiple spaces (such as the third example), I am not getting the right answer.

Answer 1

Try this

 DECLARE @Data NVARCHAR(MAX);
 SET @Data = '0000HUJ8 9IU877 8UJH'
 SELECT SUBSTRING(@Data, PATINDEX('%[^0]%', @Data), LEN(@Data))

Answer 2

One method:

STUFF(ColumnName, 1, PATINDEX('%[^0]%', ColumnName)-1, '')

---

Dan Guzman, Data Platform MVP, http://www.dbdelta.com

Answer 3

As an explanation - think differently. Rather than removing leading zeros (which is complicated), you really just want to find the first character that is NOT zero and grab everything from that position afterwards. And that is what [^0] is doing.

Answer 4

Please read a book on SQL. The concept of a tiered architecture is fundamental to this language. This sort of presentation problem should be solved in a presentation layer, not in the database.

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 5

create table test (col varchar(100))
insert into test values('00876H873 '),('00876 876500'),('0000HUJ8 9IU877 8UJH')

select * ,  Replace(REPLACE(LTRIM(REPLACE(Replace(col, ' ','*'),'0', ' ')), ' ', '0'),'*',' ') AS NumSerie from test

drop table test

Answer 6

My input is this:  '00 11011 0'

When I use this, I get an space before the start of the result:    '_11011_0'

The underscore is a space in the result. I want to remove this space at the beginning; I am okay with the later space (between the last 0 in the result and the 1 that immediately precedes it)

Answer 7

Check LTRIM and RTRIM functions in BOL.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 8

Same case as above; in the ultimate result I want the spaces to be removed at the start; I am okay with spaces in the center

Answer 9

Thanks:

I later use LTRIM to remove any leading space in the final result: for example

SELECT LTRIM(SUBSTRING('00 11011 0', PATINDEX('%[^0]%', '00 11011 0'), LEN('00 11011 0')))

I now get what I want:  11011_0

Thanks.

Answer 10

create table test (col varchar(100))
insert into test values('00876H873 '),('00876 876500'),('0000HUJ8 9IU877 8UJH'),('00 11011 0')

select * ,  LTRIM(Replace(REPLACE(LTRIM(REPLACE(Replace(col, ' ','*'),'0', ' ')), ' ', '0'),'*',' ') ) AS NumSerie from test

drop table test

Answer 11

To remove also the leading space add a space in square bracket and it is done ...

 DECLARE @Data NVARCHAR(MAX);
  SET @Data = '0000                   HUJ8 9IU877 8UJH'
  SELECT SUBSTRING(@Data, PATINDEX('%[^0 ]%', @Data), LEN(@Data))

Regards