4

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);
exexzian
  • 7,782
  • 6
  • 41
  • 52
Buzz
  • 1,549
  • 3
  • 16
  • 31

9 Answers9

12

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')
Steve Kass
  • 7,144
  • 20
  • 26
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
rsbarro
  • 27,021
  • 9
  • 71
  • 75
2

Try this

SELECT FORMAT(ColumnName,'0000') FROM TableName

Eg:

SELECT FORMAT(50,'0000')

Output: 0050

Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
ARSHAD M
  • 21
  • 3
1

%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
praveen
  • 12,083
  • 1
  • 41
  • 49
1
select RIGHT('00'+ convert(varchar, @val), 3)
Moho
  • 15,457
  • 1
  • 30
  • 31
  • I think you need `2` instead of `3` for your last parameter (if you want `01` not `001`). Otherwise this seems the most generic and readable solution to me. Can also be used for computed persisted columns (while, for some reason, FORMAT is considered non-deterministic by SQL server) – youen Jun 06 '17 at 07:53
1

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
Reema Q Khan
  • 878
  • 1
  • 7
  • 20
Vishal Kumar
  • 11
  • 1
0

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
Ken Clark
  • 2,500
  • 15
  • 15
0
SELECT REPLACE(STR(id, 4), SPACE(1), '0') AS YY FROM tableA

This would smart way to solve your problem...

Nisar
  • 5,708
  • 17
  • 68
  • 83
0
SELECT FORMAT(2, '0#')       .
Dharman
  • 30,962
  • 25
  • 85
  • 135
user18564857
  • 1