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.
-- Create sample table
CREATE TABLE Table1 (Col1 VARCHAR(100))
INSERT INTO Table1 (Col1)
SELECT '000 0001'
-- Original data
-- Remove leading zeros
SUBSTRING(Col1, PATINDEX('%[^0 ]%', Col1 + ' '), LEN(Col1))
-- Clean up
DROP TABLE Table1
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)