Tuesday, 30 July 2013

Removing Leading Zeros From Column in Table


Step 1: 

Improving the ResultSet
I had missed including all zeros in my sample set which was an overlook. Here is the new sample which includes all zero values as well.
USE tempdb
GO-- Create sample table

CREATE 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'UNION ALLSELECT '0000'

Step 2:

SELECT CASE PATINDEX('%[^0 ]%', Col1 + ' ‘')WHEN 0 THEN ''ELSE SUBSTRING(Col1, PATINDEX('%[^0 ]%', Col1 + ' '), LEN(Col1))END
FROM
Table1


See the result.........



No comments:

Post a Comment