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

Question

I have a column in my sql table. I am wondering how can I add leading zero to my column when my column's value is less than 10? So 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 Numbers with length > 2, you use a CASE expression:

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