SQL SERVER – Removing Leading Zeros From Column in Table

Posted by pinaldave on SQL Authority See other posts from SQL Authority or by pinaldave
Published on Mon, 19 Nov 2012 01:30:54 +0000 Indexed on 2012/11/19 5:09 UTC
Read the original article Hit count: 578

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)


Filed under: PostADay, SQL, SQL Authority, SQL Function, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

© SQL Authority or respective owner

Related posts about PostADay

Related posts about sql