SQL SERVER – Removing Leading Zeros From Column in Table
- by pinaldave
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.
SQL SERVER – Pad Ride Side of Number with 0 – Fixed Width Number Display
SQL SERVER – UDF – Pad Ride Side of Number with 0 – Fixed Width Number Display
SQL SERVER – Preserve Leading Zero While Coping to Excel from SSMS
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
