How to add a leading zero when the number is less than 10?

Question

I have a column in my sql table. I am wondering how can I add a leading zero to my column when the column value is less than 10? For example:

number   result
1     ->    01
2     ->    02
3     ->    03
4     ->    04
10    ->    10

Answer 1

format(number,'00')

Version> = 2012

Answer 2

You can use RIGHT:

SELECT RIGHT('0' + CAST(Number AS VARCHAR(2)), 2) FROM tbl

For Numberwith a length> 2 you use the expression CASE:

SELECT
    CASE
        WHEN Number BETWEEN 0 AND 99
            THEN RIGHT('0' + CAST(Number AS VARCHAR(2)), 2)
        ELSE
            CAST(Number AS VARCHAR(10))
    END
 FROM tbl

Answer 3

Felix , 100, case:

Select case when Number between 1 and 9 
    then '0' else '' end + cast(number as varchar(3))

Answer 4

,

select case when number <10 then replicate('0',1)+cast(number as varchar) 
else cast(number as varchar) end

Answer 5

:

SELECT REPLACE(STR(@YourNumber,2),' ','0');

, , , , :

But this does not apply to negative numbers ...

DECLARE @YourNumber INT=123;
SELECT REPLACE(STR(@YourNumber,5),' ', '0')   --pad to five digits
      ,REPLACE(STR(@YourNumber,3),' ', '0')   --pad to 3 digits
      ,REPLACE(STR(@YourNumber,2),' ', '0');  --123 is wider than 2 digits...

Result

00123   
123 
**