Earlier I wrote a blog post about Remvoing Leading Zeros from Column In Table. It was a great co-incident that my friend Madhivanan (no need of introduction for him) also post a similar article over on BeyondRelational.com. I strongly suggest to read his blog as well as he has suggested some cool solutions to the same problem.
On original blog post asked two questions 1) if my sample for testing is correct and 2) If there is any better method to achieve the same. The response was amazing. I am proud on our SQL Community that we all keep on improving on each other’s contribution. There are some really good suggestions as a comment. Let us go over them right now.
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.
-- Create sample table
CREATE TABLE Table1 (Col1 VARCHAR(100))
INSERT INTO Table1 (Col1)
SELECT '000 0001'
Now let us go over some of the fantastic solutions which we have received.
Response from Rainmaker
CASE PATINDEX('%[^0 ]%', Col1 + ' ‘')
WHEN 0 THEN ''
ELSE SUBSTRING(Col1, PATINDEX('%[^0 ]%', Col1 + ' '), LEN(Col1))
Response from Harsh Solution 1
SUBSTRING(Col1, PATINDEX('%[^0 ]%', Col1 + 'a'), LEN(Col1))
Response from Harsh Solution 2
RIGHT(Col1, LEN(Col1)+1 -PATINDEX('%[^0 ]%', Col1 + 'a' ))
Response from lucazav
, label = CAST(
CAST(REPLACE(T.Col1, ' ', '') AS FLOAT)
FROM Table1 AS T
Response from iamAkashSingh
SELECT REPLACE(LTRIM(REPLACE(col1,'0',' ')),' ','0') FROM table1
Here is the resultset of above scripts. It will remove any leading zero or space and will display the number accordingly.
If you believe there is a better solution, please leave a comment. I am just glad to see so many various responses and all of them teach us something new.
Reference: Pinal Dave (http://blog.sqlauthority.com)