How to remove leading zero from SQL
I want to remove leading zeros from a SQL table. I am connecting 3 columns as one column. Example
col1 col2 col3
00000 S Gaskin Road
N Broad Street
00001 John Rolfe Road
what i want the result to be:
1 0 S Gaskin Road or just S Gaskin Road
2 N Broad Street
3 1 John Rolfe Road
here is the script that I am concatenating 3 columns
,COALESCE(CASE WHEN col1 = '' THEN '' ELSE col1 + ' ' END, '') +
COALESCE(CASE WHEN col2 = '' THEN '' ELSE col2 + ' ' END, '') +
COALESCE(CASE WHEN col3 = '' THEN '' ELSE col3 + ' ' END, '') as allCol
source to share
cast
col1 to int
, so leading zeros are stripped off, while cast
int
- varchar
for concatenation.
COALESCE(CASE WHEN col1 = '' THEN '' ELSE cast(cast(col1 as int) as varchar(255))+ ' ' END, '') +
COALESCE(CASE WHEN col2 = '' THEN '' ELSE col2 + ' ' END, '') +
COALESCE(CASE WHEN col3 = '' THEN '' ELSE col3 + ' ' END, '')
source to share
One method uses patindex()
. My first thought on how to write this:
((case when col1 is null or col1 = '' or col1 = '000000' then ''
else substring(col1, patindex('%[^0]%', col1), 6) + ' '
end) +
(case when col2 is null or col2 = '' then ''
else col2 + ' '
end) +
(case when col3 is null or col3 = '' then ''
else col3
end)
) as allcol
If you already need to use case
, I don't see any benefit to mixing in coalesce()
.
source to share
You can define your own function to remove all leading zeros:
CREATE FUNCTION RemoveLeadingZeros(@value varchar(255)) RETURNS varchar(255)
AS
BEGIN
while substring(@value, 1, 1) = '0' and datalength(@value) > 1
begin
set @value = substring(@value, 2, 255)
end
return @value;
END
GO
So the concatenation of your three fields will now be:
,COALESCE(CASE WHEN col1 = '' THEN '' ELSE dbo.RemoveLeadingZeros(col1) + ' ' END, '') +
COALESCE(CASE WHEN col2 = '' THEN '' ELSE col2 + ' ' END, '') +
COALESCE(CASE WHEN col3 = '' THEN '' ELSE col3 + ' ' END, '') as allCol
source to share