How to add a leading 0 in sql-server

Question

I have to add a leading 0 in a column called houses, where it is a unique list of 3 digits, but I wanted have to add a leading 0 to it --> so 4 digits instead of 3. Can someone help me with this query as the code is incompatible in the '|' operator.

The code is as follows:

select houses from house_numbers
order by houses;
select houses, 
case
    when len(houses)=3 then '0' | houses
    when len(houses)=4 then '' | houses
end as houses
from house_numbers

Being new is no excuse for no research, look at the documentation for string concatenation — HoneyBadger, Apr 29 '22 at 08:34

score 2 · Answer 1 · answered Apr 29 '22 at 08:36

2

The string concatenation operator in SQL Server is +, not ||, so you should use:

CASE
    WHEN LEN(houses) = 3 THEN '0' + houses
    WHEN LEN(houses) = 4 THEN '' + houses
END AS houses

However, a better way to do this would be to just left pad with zero to a length of 4:

RIGHT('0000' + ISNULL(houses, ''), 4) AS houses

answered Apr 29 '22 at 08:36

Tim Biegeleisen

451,927
24
239
318

Thanks, Tim, very important to point out that doing case when is unnecessary here. – Jonas Metzler Apr 29 '22 at 08:37
I'm not sure replacing NULL with '0000' is required – HoneyBadger Apr 29 '22 at 08:38

score 1 · Answer 2 · answered Apr 29 '22 at 08:34

You are looking for the CONCAT function here, assuming you are dealing with varchars

when len(houses)=3 then CONCAT('0' , houses)

You could simplify like so

select houses from house_numbers
order by houses;
select houses, 
case
    when len(houses)=3 then CONCAT('0' , houses)
    else houses
end as houses
from house_numbers

score 0 · Answer 3 · answered Apr 29 '22 at 08:36

Always add the Zero and then strip of the right 4 chars, I believe this might be less resource intensive than doing a case

SELECT RIGHT('0'+CONVERT(VARCHAR(10),345),4) AS Three_Digit_Example,
       RIGHT('0'+CONVERT(VARCHAR(10),1345),4) AS Four_Digit_Example

How to add a leading 0 in sql-server

3 Answers3