November 19, 2012 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 tableCREATE TABLE Table1 (Col1 VARCHAR(100))INSERT INTO Table1 (Col1)SELECT '0001'UNION ALLSELECT '000100'UNION ALLSELECT '100100'UNION ALLSELECT '000 0001'UNION ALLSELECT '00.001'UNION ALLSELECT '01.001'GO-- Original dataSELECT *FROM Table1
GO-- Remove leading zerosSELECTSUBSTRING(Col1, PATINDEX('%[^0 ]%', Col1 + ' '), LEN(Col1))FROM Table1
GO-- Clean upDROP 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)
No comments:
Post a Comment