T-SQL Format integer to 2-digit string

user_396086 0
Viewed 137896

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.

format casting tsql
17 Answers
select right ('00'+ltrim(str( <number> )),2 )
user_2109080 
SELECT RIGHT('0' + CAST(sortexport_csv AS VARCHAR), 2)
FROM your_table
Luke_558470

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

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

here you go

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

should return 02

user_2315110

try

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

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
user_475500

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
user_1116630

Example for converting one digit number to two digit by adding 0 : 

DECLARE @int INT = 9 
        SELECT CASE WHEN @int < 10
                    THEN FORMAT(CAST(@int AS INT),'0#')
               ELSE 
                   FORMAT(CAST(@int AS INT),'0')
               END
user_118079050 
DECLARE @Number int = 1;
SELECT RIGHT('0'+ CONVERT(VARCHAR, @Number), 2)
--OR
SELECT RIGHT(CONVERT(VARCHAR, 100 + @Number), 2)
GO
user_130118030

The simplest way is:

declare
    @len int = 10,
    @number int = 122222,
    @prefix char = '0'

select replicate(@prefix, @len - len(@number)) + CAST(@number AS VARCHAR)

result:

0000122222

user_92216090
Related