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 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'
GO
-- Original data
SELECT *
FROM Table1
GO
-- Remove leading zeros
SELECT
SUBSTRING(Col1, PATINDEX('%[^0 ]%', Col1 + ' '), LEN(Col1))
FROM Table1
GO
-- Clean up
DROP 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)
Hi,
Should we check the value ’000′? PATINDEX will return zero.
SELECT
CASE PATINDEX(‘%[^0 ]%’, Col1 + ‘ ‘)
WHEN 0 THEN ”
ELSE SUBSTRING(Col1, PATINDEX(‘%[^0 ]%’, Col1 + ‘ ‘), LEN(Col1))
END
FROM Table1
As mentioned by ‘rainmaker’, For Zeros ’000′, it is not giving proper result.
For this, my suggested solution is:
SELECT
SUBSTRING(Col1, PATINDEX(‘%[^0 ]%’, Col1 + ‘a’), LEN(Col1))
FROM Table1
And for alternate solution , I have used RIGHT function,
SELECT
RIGHT(Col1, LEN(Col1)+1 -PATINDEX(‘%[^0 ]%’, Col1 + ‘a’ ))
FROM Table1
Harsh – great one buddy!
Hi,
If data is ‘ ‘, use right function will cause exception.
Because Len(‘ ‘) return 0
Yes, that will cause exception.
Updated solution:
SELECT
RIGHT(Col1, LEN(Col1+’a') -PATINDEX(‘%[^0 ]%’, Col1 + ‘a’ ))
FROM Table1
what does ^0 mean in t- sql
It means that Find the first position a character in a string which is not 0
This is another way to do that:
SELECT
T.Col1
, label = CAST(
CAST(REPLACE(T.Col1, ‘ ‘, ”) AS FLOAT)
AS VARCHAR(10))
FROM Table1 AS T
Regards
^0 means Pattern not like 0
Basically get the first Non 0 or Non Space character(If you see carefully there is a space after 0 in the pattern), take its index position in the string and then using substring, retrieve anything after that.
Similar post of mine is http://beyondrelational.com/modules/2/blogs/70/posts/18203/removing-leading-zeroes-in-alpha-numeric-string.aspx
wonderful. I recently attended an interview for data analyst position and got the same question. It was interesting. But the question was to remove both the leading and trailing zeros from a string column having only numeric charecters.
The answer I gave was
select reverse(convert(int,reverse(convert(int,col1)))) from table1
The idea is converting a column to int will remove the leading zeros.
Pinal Sir, please let me know if there can be a beter solution :)
This will indeed work but if there are alphanumeric character it will not work.
But great solution indeed – it will work in my use case which I wrote in blog post! Good Job!
One more thing above solution will not handle space as well decimal points.
And , this solution will not work when no is larger than maximum integer value (2147483647).
Thanks for pointing out the limitations Pinal and Harsh.
I got a similar question when I went for an interview but the question was to remove both the leading and trailing zeros in a string column with numeric charecters. The solution I gave is this
select reverse(convert(int,reverse(convert(int,col1)))) from table1
Please let me know if there is a better solution
Hi,
i have an alternate solution.
Replace(Ltrim(Replace(col1,’0′,’ ‘)),’ ‘,’0′)
Thanks and Regards,
Akash K Singh
Pingback: SQL SERVER – Removing Leading Zeros From Column in Table – Part 2 « SQL Server Journey with SQL Authority
In ur case, ’00.001′ will be ‘.001′.
Sometimes it should be ’0.001′.
How about my script here?
SELECT CASE WHEN ISNUMERIC(REPLACE(Col1,’ ‘,”)) = 0 THEN Col1
ELSE CONVERT(varchar,
CASE WHEN CHARINDEX(‘.’,REPLACE(Col1,’ ‘,”)) > 0 THEN CAST(REPLACE(Col1,’ ‘,”) AS float)
ELSE CAST(REPLACE(Col1,’ ‘,”) AS int)
END)
END
FROM Table1
If Col1 contains currency symbols , it will throw exception.
Select cast(replace(col1,’ ‘,’0′) as money) from Table1
Hello, I am a rookie for SQL server, who can tell me why a space is added to ‘Col1′ on Pinal’s solution. I tried some samples it works even without adding space. “SUBSTRING(Col1, PATINDEX(‘%[^0 ]%’, Col1 + ‘ ‘), LEN(Col1))”
SELECT SUBSTRING(Col1, PATINDEX(‘%[^0]%’, Col1 + ‘ ‘), LEN(Col1))
FROM ( SELECT replace(Col1, ‘ ‘, ” ) AS Col1 FROM Table1 ) as t
SELECT *
FROM Table1
GO
SELECT
CASE WHEN SUBSTRING(Col1, PATINDEX(‘%[^0]%’, Col1+’.'), LEN(Col1)) = ”
THEN ‘ ‘ ELSE SUBSTRING(Col1, PATINDEX(‘%[^0]%’, Col1+’.'), LEN(Col1)) END AS Col1
FROM ( SELECT replace(Col1, ‘ ‘, ” ) AS Col1 FROM Table1 ) as no_space
Go
Col1
0001
000100
100100
000 0001
00.001
01.001
000
0
Original
Col1
1
100
100100
1
.001
1.001
After removing leading zeros