Removing leading zeros in a string in sqlserver

Question

I want to remove leading zeros for a varchar column. Actually we are storing version information in a column. Find below example versions.

2.00.001
The output would be : 2.0.1

Input : 2.00.00.001
The output would be: 2.0.0.1

Input : 2.00
The output would be : 2.0

The dots in the version column not constant. It may be two or three or four

I found some solutions in google but those are not working. Find below are the queries I tried.

SELECT SUBSTRING('2.00.001', PATINDEX('%[^0 ]%', '2.00.001' + ' '), LEN('2.00.001'))

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

Please suggest me the best approach in sqlserver.

i think you are going to need a split string function to convert the values to numbers before putting them back together again. — Tanner, Feb 09 '17 at 09:37

score 2 · Answer 1 · edited May 23 '17 at 10:29

One way is to use a string splitting function with cross apply, for xml path, and stuff.
For an explanation on how stuff and for xml works together to concatenate a string from selected rows, read this SO post.

Using a string splitting function will enable you to convert each number part of the string to int, that will remove the leading zeroes. Executing a select statement on the result of the string splitting function will enable you to get your int values back into a varchar value, seperated by dot. The stuff function will remove the first dot.

Create the string splitting function:

CREATE FUNCTION SplitStrings_XML
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );
GO

I've chosen to use an xml based function because it's fairly simple. If you are using 2016 version you can use the built in string_split function. For earlier versions, I would stronly suggest reading Aaron Bertrand's Split strings the right way – or the next best way.

Create and populate sample table (Please save us this step in your future questions)

DECLARE @T AS TABLE
(
    col varchar(20)
)

INSERT INTO @T VALUES
('2.00.001'),
('2.00.00.001'),
('2.00')

The query:

SELECT col, result
FROM @T
CROSS APPLY 
(
    SELECT STUFF(
    (
    SELECT '.' + CAST(CAST(Item as int) as varchar(20)) 
    FROM SplitStrings_XML(col, '.')
    FOR XML PATH('')
    )
    , 1, 1, '') As result

) x

Results:

col             result
2.00.001        2.0.1
2.00.00.001     2.0.0.1
2.00            2.0

You can see it in action on this link on rextester

doesn't work for this: `DECLARE @val AS NVARCHAR(20) = '2.00.00.001'; SELECT REPLACE(REPLACE(REPLACE(@val, '00', '><'), '<>', ''), '><', '0') as string` — Tanner, Feb 09 '17 at 09:43
@Tanner I've changed my answer to a completly different approach. — Zohar Peled, Feb 09 '17 at 09:54
FYI – for this specific case (version numbers separated by dots) there is simpler solution with less unnecessary overhead which also performs faster. I have added it as [answer](http://stackoverflow.com/a/42134488/2392157). Still your solution can be useful for programmers learning those classic SQL Server tricks with strings. — miroxlav, Feb 09 '17 at 13:42

John Cappelletti · Answer 2 · 2017-02-09T10:40:12.053

No need for Split/Parse Function, and easy to expand if there could be more than 5 groups

Declare @YourTable table (YourCol varchar(25))
Insert Into @YourTable Values
('2.00.001'),
('2.00.00.001'),
('2.00')

Update @YourTable
       Set YourCol = concat(Pos1,'.'+Pos2,'.'+Pos3,'.'+Pos4,'.'+Pos5)
 From  @YourTable A
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','int')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','int')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','int')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','int')))
                      ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','int')))
                From  (Select Cast('<x>' + replace((Select replace(A.YourCol,'.','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

Select * from @YourTable

Returns

YourCol
2.0.1
2.0.0.1
2.0

score 1 · Answer 3 · edited May 23 '17 at 11:47

Easy, fast, compatible and readable way – without tables or XML tricks.

Correctly handles all cases including empty string, NULL, or numbers like 00100.
Supports unlimited number of groups. Runs on all SQL Server versions.

Step 1: Remove leading zeros from all groups.
Step 2: Place single zero to groups where no digits remained.

[Edit: Not sure why it was downvoted twice. Check the solution: ]

The function:

CREATE FUNCTION dbo.fncGetNormalizedVersionNumber(@Version nvarchar(200))
    RETURNS nvarchar(200) AS
BEGIN
    -- Preprocessing: Surround version string by dots so all groups have the same format.
    SET @Version = '.' + @Version + '.';

    -- Step 1: Remove any leading zeros from groups as long as string length decreases.
    DECLARE @PreviousLength int = 0;
    WHILE @PreviousLength <> LEN(@Version)
    BEGIN
        SET @PreviousLength = LEN(@Version);
        SET @Version = REPLACE(@Version, '.0', '.');
    END;

    -- Step 2: Insert 0 to any empty group as long as string length increases.
    SET @PreviousLength = 0;
    WHILE @PreviousLength <> LEN(@Version)
    BEGIN
        SET @PreviousLength = LEN(@Version);
        SET @Version = REPLACE(@Version, '..', '.0.');
    END;

    -- Strip leading and trailing dot added by preprocessing.
    RETURN SUBSTRING(@Version, 2, LEN(@Version) - 2);
END;

Usage:

SELECT dbo.fncGetNormalizedVersionNumber('020.00.00.000100');

20.0.0.100

Performance per 100,000 calculations:

solution using helper function + helper tables + XML: 54519 ms
this solution (used on table column): 2574 ms (→ 21 times faster) (UPDATED after comment.)

While this solution might work faster for a single string variable, It's not going to be easy to scale it to work with all rows in a table. Also, It will not handle the `2.00` -> `2.0` requested by the OP correctly. It will convert it to `2.` — Zohar Peled, Feb 09 '17 at 14:58
@ZoharPeled – maybe you made a mistake in your guess, beucase the program gives `2.00` → `2.0`. Performance on table column is 21 times better than performance of the linked overhead solution (metric updated). Not mentioning lesser mental effort on decoding the source by human. — miroxlav, Feb 09 '17 at 15:26

Lobo · Answer 4 · 2017-02-12T00:27:05.027

For SQL Server 2016:

SELECT STUFF ((SELECT '.' + CAST(CAST(value AS INT) AS VARCHAR) FROM STRING_SPLIT('2.00.001', '.') FOR XML PATH ('')) , 1, 1, '')

According to this: https://sqlperformance.com/2016/03/sql-server-2016/string-split

It's the fastest way :) Aaron Bertrand knows it's stuff.

For an interesting and deep read about splitting strings on SQL Server plese read this gem of knowledge: http://www.sqlservercentral.com/articles/Tally+Table/72993/

It has some clever strategies

score 0 · Answer 5 · edited Feb 09 '17 at 11:32

0

Try this one

SUBSTRING(str_col, PATINDEX('%[^0]%', str_col+'.'), LEN(str_col))

edited Feb 09 '17 at 11:32

stealthjong

10,858
13
45
84

answered Feb 09 '17 at 09:28

Khushbu_sipl

123
8

select SUBSTRING('2.00.001', PATINDEX('%[^0]%', '2.00.001'+'.'), LEN('2.00.001')) – Vijay Feb 09 '17 at 09:31
Output is giving: 2.00.001. but i need 2.0.1 – Vijay Feb 09 '17 at 09:32

Pawel Czapski · Answer 6 · 2017-02-09T10:24:30.627

0

I am not sure this is what you are looking for but you can give a go, it should handle up to 4 zeros.

DECLARE @VERSION NVARCHAR(20) = '2.00.00.001'
SELECT REPLACE(REPLACE(REPLACE(@VERSION, '0000','0'),'000','0'),'00','0')

2.0.0.01

SET @VERSION = '2.00.00.01'
SELECT REPLACE(REPLACE(REPLACE(@VERSION, '0000','0'),'000','0'),'00','0')

2.0.0.01

SET @VERSION = '2.000.0000.0001'
SELECT REPLACE(REPLACE(REPLACE(@VERSION, '0000','0'),'000','0'),'00','0')

2.0.0.01

edited Feb 09 '17 at 10:24

answered Feb 09 '17 at 10:13

Pawel Czapski

1,856
2
16
26

Do we want to create `10` from `100`? – miroxlav Feb 09 '17 at 10:17
@miroxlav, Not shoure what you are about here? Did you test it? I just noticed it will not remove zeros before last grater than zero digit, but I am working on it – Pawel Czapski Feb 09 '17 at 10:23
Try with `SET @VERSION = '2.000.0000.100'`. It leads to incorrect result. – miroxlav Feb 09 '17 at 10:31
Question is about to remove leading zeros so make 1 from 001, not 1 from 100. – Pawel Czapski Feb 09 '17 at 10:38
If you read the question carefully, input is a version number. Your solution will work only on when any part of the version number will not exceed 99. But would you place solution with such a special constraint into your own source code? – miroxlav Feb 09 '17 at 11:14
No I wouldn't, It was only "not perfect" idea how to resolve an issue. – Pawel Czapski Feb 09 '17 at 11:33

score 0 · Answer 7 · answered Feb 10 '17 at 01:38

Here is another sample:

CREATE TABLE #tt(s VARCHAR(15))
INSERT INTO  #tt VALUES
('2.00.001'),
('2.00.00.001'),
('2.00')

SELECT t.s,STUFF(c.s,1,1,'') AS news FROM #tt AS t
OUTER APPLY(
       SELECT '.'+LTRIM(z.n) FROM (VALUES(CONVERT(XML,'<n>'+REPLACE(t.s,'.','</n><n>')+'</n>'))) x(xs)
       CROSS APPLY(SELECT n.value('.','int') FROM x.xs.nodes('n') AS y(n)) z(n)
       FOR XML PATH('')
) c(s)

s               news
--------------- -----------
2.00.001        2.0.1
2.00.00.001     2.0.0.1
2.00            2.0

Removing leading zeros in a string in sqlserver

7 Answers7

Easy, fast, compatible and readable way – without tables or XML tricks.