Have you ever encountered a situation where you needed to remove leading zeros from a column in a SQL Server table? If so, you’re not alone. This is a common problem that many SQL Server developers face. In this article, we will explore different solutions to this problem and discuss their pros and cons.

In a previous blog post, I discussed one approach to removing leading zeros from a column in a table. However, my friend Madhivanan also wrote an article on the same topic on BeyondRelational.com, where he shared some interesting solutions. I highly recommend reading his blog post as well, as it provides additional insights into this problem.

Let’s start by revisiting the sample data used in the original blog post. I realized that I had overlooked including values with all zeros in my sample set. Here is an updated sample that includes such values:

USE tempdb
GO

-- Create sample table
CREATE TABLE Table1 (
    Col1 VARCHAR(100)
)

INSERT INTO Table1 (Col1)
VALUES
    ('0001'),
    ('000100'),
    ('100100'),
    ('000 0001'),
    ('00.001'),
    ('01.001'),
    ('0000')
GO

Now, let’s explore some of the fantastic solutions that we have received from the SQL community:

Solution 1: Provided by Rainmaker

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

Solution 2: Provided by Harsh

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

Solution 3: Provided by Harsh

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

Solution 4: Provided by lucazav

SELECT T.Col1, label = CAST(CAST(REPLACE(T.Col1, ' ', '') AS FLOAT) AS VARCHAR(10))
FROM Table1 AS T

Solution 5: Provided by iamAkashSingh

SELECT REPLACE(LTRIM(REPLACE(col1, '0', ' ')), ' ', '0')
FROM table1

Each of these solutions will remove any leading zeros or spaces from the column and display the number accordingly. It’s fascinating to see the variety of responses we received, and each of them teaches us something new.

If you believe there is a better solution or have any other insights to share, please leave a comment below. The SQL Server community thrives on collaboration and continuous improvement, and your contribution is valuable.

Thank you for reading!