T-SQL Format integer to 2-digit string

Question

I can't find a simple way to do this in T-SQL.

I have for example a column (SortExport_CSV) that returns an integer '2' thru 90. If the stored number is a single digit, I need it to convert to a 2 digit string that begins with a 0. I have tried to use CAST but I get stuck on how to display the style in the preferred format (0#)

Of course it is easy to do this on the front end (SSRS, MSAccess, Excel, etc) but in this instance I have no front end and must supply the raw dataset with the already formatted 2 digit string.

Answer 1

select right ('00'+ltrim(str( <number> )),2 )

Answer 2

here you go

select RIGHT(REPLICATE('0', 2) + CAST(2 AS VARCHAR(2)), 2)

should return 02

Answer 3

Convert the value to a string, add a zero in front of it (so that it's two or tree characters), and get the last to characters:

right('0'+convert(varchar(2),Sort_Export_CSV),2)

Answer 4

try

right('0' + convert(varchar(2), @number),2)

Answer 5

SELECT RIGHT('0' + CAST(sortexport_csv AS VARCHAR), 2)
FROM your_table

Answer 6

Another example:

select 
case when teamId < 10 then '0' + cast(teamId as char(1)) 
else cast(teamId as char(2)) end      
as 'pretty id',
* from team

Answer 7

Here is tiny function that left pad value with a given padding char You can specify how many characters to be padded to left..

   Create    function fsPadLeft(@var varchar(200),@padChar char(1)='0',@len int)
      returns varchar(300)
    as
    Begin
      return replicate(@PadChar,@len-Len(@var))+@var
    end

To call :

declare @value int; set @value =2
select dbo.fsPadLeft(@value,'0',2)

Answer 8

Try this

--Generate number from 2 to 90

;with numcte as(
select 2 as rn
union all
select rn+1 from numcte where rn<90)

--Program that formats the number based on length

select case when LEN(rn) = 1 then '00'+CAST(rn as varchar(10)) else CAST(rn as varchar(10)) end number
from numcte

Partial Output:

number    
002
003
004
005
006
007
008
009
10
11
12
13
14
15
16
17
18
19
20