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 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 
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 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 
houses?