T-SQL Format integer to 2-digit string

问题:

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.

回答1:

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

回答2:

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

回答3:

You can use T-SQL's built in format function:

declare @number int  = 1
select format (@number, '0#')

回答4:

You're all doing too much work:

right(str(100+@x),2)

-- for a function, same idea: 
--
create function zeroPad( @yourNum int, @wid int)
as 
begin
  return right( 1000000+@yourNum), @wid)
end

回答5:

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)

回答6:

DECLARE @Number int = 1;
SELECT RIGHT('0'+ CONVERT(VARCHAR, @Number), 2)
--OR
SELECT RIGHT(CONVERT(VARCHAR, 100 + @Number), 2)
GO

回答7:

You could try this

SELECT RIGHT( '0' + convert( varchar(2) , '0' ),  2 ) -- OUTPUTS : 00
SELECT RIGHT( '0' + convert( varchar(2) , '8' ),  2 ) -- OUTPUTS : 08
SELECT RIGHT( '0' + convert( varchar(2) , '9' ),  2 ) -- OUTPUTS : 09
SELECT RIGHT( '0' + convert( varchar(2) , '10' ), 2 ) -- OUTPUTS : 10
SELECT RIGHT( '0' + convert( varchar(2) , '11' ), 2 ) -- OUTPUTS : 11

this should help

回答8:

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

回答9:

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)

回答10:

here you go

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

should return 02

回答11:

try

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

回答12:

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

回答13:

SELECT
replace(str(month(DATEADD(month, -1, '2012-02-29')), 2),' ' , '0')

回答14:

You can create a function like this:

CREATE FUNCTION [dbo].[f_convert_int_to_2_digits](@input int)
RETURNS varchar(10)
AS
BEGIN
    --return value
    DECLARE @return varchar(10)

    if @input < 10
    begin
    set @return = '0' + convert(varchar(1), @valor)
end
    else
    begin
        set @return = convert(varchar(10), @input)
    end

    -- return result
    RETURN @return

END

and then use it everywhere:
select [dbo].[f_convert_int_to_2_digits](<some int>)