'How to add a leading 0 in sql-server [duplicate]

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
sqlsql-server


Solution 1:[1]

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

Solution 2:[2]

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

Solution 3:[3]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Tim Biegeleisen
Solution 2 JohanB
Solution 3 Ockert

Related Questions