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 about leading zeros.

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 the user in this example.

Solarwinds
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
GO

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

SQL SERVER - Removing Leading Zeros From Column in Table leadingzeroes

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 (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQLAuthority News – Microsoft SQL Server 2012 Service Pack 1 Released (SP1)
Next Post
SQL SERVER – Using RAND() in User Defined Functions (UDF)

Related Posts

37 Comments. Leave new

  • 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

    Reply
  • 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

    Reply
  • what does ^0 mean in t- sql

    Reply
  • 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

    Reply
  • ^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.

    Reply
  • Similar post of mine is

    Reply
  • naveen2coolNaveen
    November 20, 2012 11:57 am

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

    Reply
  • 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

    Reply
  • Hi,

    i have an alternate solution.

    Replace(Ltrim(Replace(col1,’0′,’ ‘)),’ ‘,’0’)

    Thanks and Regards,
    Akash K Singh

    Reply
  • 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

    Reply
  • Samith C Valsalan
    December 14, 2012 12:00 pm

    Select cast(replace(col1,’ ‘,’0’) as money) from Table1

    Reply
  • 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))”

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

      Reply
    • 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.

      Reply
  • 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

    Reply
  • 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

    Reply
  • Pinal Sir ,

    I am using below script , is it good

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

    Reply
  • 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’

    Reply
  • alter table [dbo].[employee3-20-12-2014]
    add empno1 as (SELECT

    SUBSTRING(empNo, PATINDEX(‘%[^0 ]%’, empNo + ‘ ‘), LEN(empNo))

    FROM [employee3-20-12-2014])

    Msg 1046, Level 15, State 1, Line 2
    Subqueries are not allowed in this context. Only scalar expressions are allowed.

    Reply
  • I did remove the leading zeros by running the below sql script

    UPDATE KPLC_NEW SET ACCOUNTNO = SUBSTRING(ACCOUNTNO, PATINDEX(‘%[^0 ]%’, ACCOUNTNO + ‘ ‘), LEN(ACCOUNTNO))

    Reply
  • Good Article.. It is really very helpfull!! Thanks

    Reply
  • Hi Pinal, I have an issue with Oracle dropping my leading zeroes and adding them to the end for some reason. The column is number column. In udb the data is fine but once in oracle the values change.. any ideas?

    Reply
    • Steve – I have very limited knowledge of Oracle and I have no idea about the issue which you are facing.

      Reply

Leave a Reply

Menu