SQL SERVER – Removing Leading Zeros From Column in Table

Some questions surprises me and make me write code which I have never explored before. Today was similar experience as well. I have always received the question regarding how to reserve leading zeroes in SQL Server while displaying them on the SSMS or another application. I have written articles on this subject over here.

Today I received a very different question where the user wanted to remove leading zero and white space. I am using the same sample sent by user in this example.

USE tempdb
GO
-- Create sample table
CREATE TABLE Table1 (Col1 VARCHAR(100))
INSERT INTO Table1 (Col1)
SELECT '0001'
UNION ALL
SELECT '000100'
UNION ALL
SELECT '100100'
UNION ALL
SELECT '000 0001'
UNION ALL
SELECT '00.001'
UNION ALL
SELECT '01.001'
GO
-- Original data
SELECT *
FROM Table1
GO
-- Remove leading zeros
SELECT
SUBSTRING(Col1, PATINDEX('%[^0 ]%', Col1 + ' '), LEN(Col1))
FROM Table1
GO
-- Clean up
DROP TABLE Table1
GO

Here is the resultset of above script. It will remove any leading zero or space and will display the number accordingly.

This problem is a very generic problem and I am confident there are alternate solutions to this problem as well. If you have an alternate solution or can suggest a sample data which does not satisfy the SUBSTRING solution proposed, I will be glad to include them in follow up blog post with due credit.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

29 thoughts on “SQL SERVER – Removing Leading Zeros From Column in Table

  1. Hi,
    Should we check the value ’000′? PATINDEX will return zero.

    SELECT
    CASE PATINDEX(‘%[^0 ]%’, Col1 + ‘ ‘)
    WHEN 0 THEN ”
    ELSE SUBSTRING(Col1, PATINDEX(‘%[^0 ]%’, Col1 + ‘ ‘), LEN(Col1))
    END
    FROM Table1

  2. As mentioned by ‘rainmaker’, For Zeros ’000′, it is not giving proper result.
    For this, my suggested solution is:

    SELECT
    SUBSTRING(Col1, PATINDEX(‘%[^0 ]%’, Col1 + ‘a’), LEN(Col1))
    FROM Table1

    And for alternate solution , I have used RIGHT function,

    SELECT
    RIGHT(Col1, LEN(Col1)+1 -PATINDEX(‘%[^0 ]%’, Col1 + ‘a’ ))
    FROM Table1

  3. This is another way to do that:

    SELECT
    T.Col1
    , label = CAST(
    CAST(REPLACE(T.Col1, ‘ ‘, ”) AS FLOAT)
    AS VARCHAR(10))
    FROM Table1 AS T

    Regards

  4. ^0 means Pattern not like 0
    Basically get the first Non 0 or Non Space character(If you see carefully there is a space after 0 in the pattern), take its index position in the string and then using substring, retrieve anything after that.

  5. wonderful. I recently attended an interview for data analyst position and got the same question. It was interesting. But the question was to remove both the leading and trailing zeros from a string column having only numeric charecters.

    The answer I gave was

    select reverse(convert(int,reverse(convert(int,col1)))) from table1

    The idea is converting a column to int will remove the leading zeros.

    Pinal Sir, please let me know if there can be a beter solution :)

  6. I got a similar question when I went for an interview but the question was to remove both the leading and trailing zeros in a string column with numeric charecters. The solution I gave is this

    select reverse(convert(int,reverse(convert(int,col1)))) from table1

    Please let me know if there is a better solution

  7. Pingback: SQL SERVER – Removing Leading Zeros From Column in Table – Part 2 « SQL Server Journey with SQL Authority

  8. In ur case, ’00.001′ will be ‘.001′.
    Sometimes it should be ’0.001′.
    How about my script here?

    SELECT CASE WHEN ISNUMERIC(REPLACE(Col1,’ ‘,”)) = 0 THEN Col1
    ELSE CONVERT(varchar,
    CASE WHEN CHARINDEX(‘.’,REPLACE(Col1,’ ‘,”)) > 0 THEN CAST(REPLACE(Col1,’ ‘,”) AS float)
    ELSE CAST(REPLACE(Col1,’ ‘,”) AS int)
    END)
    END
    FROM Table1

  9. Hello, I am a rookie for SQL server, who can tell me why a space is added to ‘Col1′ on Pinal’s solution. I tried some samples it works even without adding space. “SUBSTRING(Col1, PATINDEX(‘%[^0 ]%’, Col1 + ‘ ‘), LEN(Col1))”

    • SELECT SUBSTRING(Col1, PATINDEX(‘%[^0]%’, Col1 + ‘ ‘), LEN(Col1))
      FROM ( SELECT replace(Col1, ‘ ‘, ” ) AS Col1 FROM Table1 ) as t

    • Hullo Eileena, I puzzled about this too until I re-read BOL. It states “If either pattern or expression is NULL, PATINDEX returns NULL.” The addition of a space ensures that neither returns null. In Pinal’s solution the pattern being searched for is either zero or space and by adding a space to the expression, PATINDEX will never return NULL.

  10. SELECT *
    FROM Table1
    GO

    SELECT
    CASE WHEN SUBSTRING(Col1, PATINDEX(‘%[^0]%’, Col1+’.’), LEN(Col1)) = ”
    THEN ‘ ‘ ELSE SUBSTRING(Col1, PATINDEX(‘%[^0]%’, Col1+’.’), LEN(Col1)) END AS Col1
    FROM ( SELECT replace(Col1, ‘ ‘, ” ) AS Col1 FROM Table1 ) as no_space

    Go

    Col1
    0001
    000100
    100100
    000 0001
    00.001
    01.001
    000
    0
    Original

    Col1
    1
    100
    100100
    1
    .001
    1.001

    After removing leading zeros

  11. Create a function for reusability, and you can change the core line of code with whatever fits you needs. This is my suggestion, easy to read, but probably not the most optimized one.

    create function dbo.fTrimZeros(@string varchar(255))
    returns varchar(255)
    begin
    while (left(@string,1)=’0′) begin set @string = right(@string, len(@string)-1) end
    return @string
    end

    select dbo.fTrimZeros(’000L2323′)

    returns L2323

  12. Pinal Sir ,

    I am using below script , is it good

    replace(ltrim(replace(rtrim(’00011′),’0′,’ ‘)),’ ‘,’0′)

  13. Hi guys…i am getting this error

    ‘Msg 8116, Level 16, State 1, Procedure proc_INT_ROA_Spend_Cleaup, Line 10
    Argument data type float is invalid for argument 2 of patindex function’

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s