How to cast a number to string with 0 prefix in sql server

Question

In sql server, if I have a number n, and n>=0 and n<=100, how to cast it into a string with sql?

1 => '01'
2 => '02'
...
10 => '10'
99 => '99'

It is something like printf.

printf ('%2d", n);

Answer 1

So long as n is strictly less than 100, you can use

  RIGHT(100+n,2)

In SQL Server 2012, you can also use FORMAT, and the following will work and also give you '100' for 100.

  FORMAT(n,'00','en-US')

Answer 2

Yet another approach (not sure how you want to handle numbers greater than 99):

DECLARE @numbers AS TABLE(Val INT)
INSERT INTO @numbers VALUES(1)
INSERT INTO @numbers VALUES(2)
INSERT INTO @numbers VALUES(10)
INSERT INTO @numbers VALUES(11)
INSERT INTO @numbers VALUES(98)
INSERT INTO @numbers VALUES(99)
INSERT INTO @numbers VALUES(100)
SELECT REPLACE(STR(Val, 2), ' ', '0') FROM @numbers

Answer 3

%2d needs to translated to 2 digit format in sql server

Try this

Declare @val int
set @val=1
SELECT RIGHT('0' + CAST(@val AS VARCHAR(2)), 2)

Result :-

01

In order to check for numbers between 0 to 100 use case statement

SELECT  case when @val>0 and @val<100 then 
               RIGHT('0' + CAST(@val AS VARCHAR), 2)
               else 
               NULL
               END

Answer 4

select RIGHT('00'+ convert(varchar, @val), 3)

Answer 5

you can use LPAD function of sql

Try this

SELECT LPAD(n, 2, 0);

Result
01
02
....
10

Explanation : The LPAD function Left-pad the string with 3rd parameter( i.e 0 in given example) up to length given in 2nd parameter ( i.e 2 in given example)

more example

SELECT LPAD('hello', 10, '*');
Result :  *****hello

Answer 6

Do this:

DECLARE @table AS TABLE(n INT)
INSERT INTO @table 
VALUES(1),(3),(5),(9),(10),(50),(99),(100)

Select Case When n<10 Then '0'+Cast(n as varchar(10)) Else Cast(n as varchar(10)) End From @table

Answer 7

SELECT REPLACE(STR(id, 4), SPACE(1), '0') AS YY FROM tableA

This would smart way to solve your problem...

Answer 8

SELECT FORMAT(2, '0#')       .