CODEWITHSUNDEEP
sqlsql-server
Buzz
Buzz

Reputation: 1619

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

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);

Upvotes: 4

Views: 49009

Answers (10)

gilgarola
gilgarola

Reputation: 23

If you are working with 2 integers, e.g. month numbers:

SELECT RIGHT(CAST(MONTH(GETDATE())+100 AS VARCHAR(3)),2)

"MONTH(GETDATE())" being any expression.

Upvotes: 0

ARSHAD M
ARSHAD M

Reputation: 21

Try this

SELECT FORMAT(ColumnName,'0000') FROM TableName

Eg:

SELECT FORMAT(50,'0000')

Output: 0050

Upvotes: 2

user18564857
user18564857

Reputation: 1

SELECT FORMAT(2, '0#')       .

Upvotes: 0

Vishal Kumar
Vishal Kumar

Reputation: 11

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

Upvotes: 1

Nisar
Nisar

Reputation: 6038

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

This would smart way to solve your problem...

Upvotes: 0

Ken Clark
Ken Clark

Reputation: 2530

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

Upvotes: 0

praveen
praveen

Reputation: 12271

%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

Upvotes: 2

rsbarro
rsbarro

Reputation: 27339

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

Upvotes: 2

Steve Kass
Steve Kass

Reputation: 7184

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')

Upvotes: 13

Moho
Moho

Reputation: 16498

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

Upvotes: 2

Related Questions