Removing Leading Zeros

Posted by on Dec 31, 2012 in SQL | 0 comments

Removing Leading Zeros From Column in Table

 

USE tempdb
GO
-- Create sample table
CREATE TABLE Table1 (Col1 VARCHAR(100))
INSERT INTO Table1 (Col1)
SELECT '000006'
UNION ALL
SELECT '00501007'
UNION ALL
SELECT '50401003'
UNION ALL
SELECT '0100 000 001'
UNION ALL
SELECT '00.0001'
UNION ALL
SELECT '07.001'
GO

-- Display the data from the new table

SELECT *
FROM Table1
GO

-- Remove leading zeros
SELECT
SUBSTRING(Col1, PATINDEX('%[^0 ]%', Col1 + ' '), LEN(Col1))
FROM Table1
GO


-- Clean up
DROP TABLE Table1
GO

 

Leave a Reply