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 about leading zeros.
- 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 the 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
GOHere is the result set 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 (https://blog.sqlauthority.com)
37 Comments. Leave new
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
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
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 :)
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
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
Hullo Eileena, I puzzled about this too until I re-read BOL. It states “If either pattern or expression is NULL, PATINDEX returns NULL.” The addition of a space ensures that neither returns null. In Pinal’s solution the pattern being searched for is either zero or space and by adding a space to the expression, PATINDEX will never return NULL.
Thank you Rowan!
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
Create a function for reusability, and you can change the core line of code with whatever fits you needs. This is my suggestion, easy to read, but probably not the most optimized one.
create function dbo.fTrimZeros(@string varchar(255))
returns varchar(255)
begin
while (left(@string,1)=’0′) begin set @string = right(@string, len(@string)-1) end
return @string
end
select dbo.fTrimZeros(‘000L2323’)
returns L2323
Pinal Sir ,
I am using below script , is it good
replace(ltrim(replace(rtrim(‘00011′),’0′,’ ‘)),’ ‘,’0’)
alter table [dbo].[employee3-20-12-2014]
add empno1 as (SELECT
SUBSTRING(empNo, PATINDEX(‘%[^0 ]%’, empNo + ‘ ‘), LEN(empNo))
FROM [employee3-20-12-2014])
Msg 1046, Level 15, State 1, Line 2
Subqueries are not allowed in this context. Only scalar expressions are allowed.
@shbnq422 – What is your end goal?
I did remove the leading zeros by running the below sql script
UPDATE KPLC_NEW SET ACCOUNTNO = SUBSTRING(ACCOUNTNO, PATINDEX(‘%[^0 ]%’, ACCOUNTNO + ‘ ‘), LEN(ACCOUNTNO))
Good Article.. It is really very helpfull!! Thanks
I am glad that it helped you SS.
Hi Pinal, I have an issue with Oracle dropping my leading zeroes and adding them to the end for some reason. The column is number column. In udb the data is fine but once in oracle the values change.. any ideas?
Steve – I have very limited knowledge of Oracle and I have no idea about the issue which you are facing.
hi pinal – I have one issue i.e I want to remove top 3 leading zeros from a string in sql. ex: 001,012,0002,00004 then I need the result as 1,12,2,04