SQL SERVER – Removing Leading Zeros From Column in Table – Part 2

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.

USE tempdb
GO
-- Create sample table
CREATE TABLE Table1 (Col1 VARCHAR(100))
INSERT INTO Table1 (Col1)
SELECT '0001'
UNION ALL
SELECT '000100'
UNION ALL
SELECT '100100'
UNION ALL
SELECT '000 0001'
UNION ALL
SELECT '00.001'
UNION ALL
SELECT '01.001'
UNION ALL
SELECT '0000'
GO

Now let us go over some of the fantastic solutions which we have received.

Solarwinds

Response from Rainmaker

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

Response from Harsh Solution 1

SELECT
SUBSTRING(Col1, PATINDEX('%[^0 ]%', Col1 + 'a'), LEN(Col1))
FROM Table1

Response from Harsh Solution 2

SELECT
RIGHT(Col1, LEN(Col1)+1 -PATINDEX('%[^0 ]%', Col1 + 'a'))
FROM Table1

Response from lucazav

SELECT
T.Col1
, label = CAST(
CAST(REPLACE(T.Col1, ' ', '') AS FLOAT)
AS VARCHAR(10))
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.

SQL SERVER - Removing Leading Zeros From Column in Table - Part 2 leadingzeroes

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 (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER – Using RAND() in User Defined Functions (UDF)
Next Post
SQLAuthority News – Android Efficiency Tips and Tricks – Personal Technology Tip

Related Posts

6 Comments. Leave new

  • how about cast as float?

    Reply
  • Boregowda, Santhoshkumar
    November 22, 2012 11:52 am

    It seems Unsubscribe link is not redirecting to the correct page.

    I want to stop sending emails to this email ID, I have subscribed for another one.

    Could you please help stop?

    ________________________________

    Reply
  • Samith C Valsalan
    December 14, 2012 11:52 am

    Select cast(replace(col1,’ ‘,’0’) as money) from Table1

    Reply
  • The issue is that in real life as in Murphy’s law, where you can have a varchar, you sometimes get a non-numeric value. Meaning that its not safe to convert to a numeric by assuming the data passing through is always going to be a number. Along comes a non-numeric value, and your query goes kaboom. So I agree with @iamAkashSingh since its a fully text only solution and doest involve conversion to float/numeric/money as this level

    Reply
  • Out of the 5-6 methods which is most efficient in retrieving data?

    Reply

Leave a Reply

Menu