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

36 Comments. Leave new

  • hi pinal – I have one issue i.e I want to remove top 3 leading zeros from a string in sql. ex: 001,012,0002,00004 then I need the result as 1,12,2,04

    Reply

Leave a Reply

Menu