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

User · Answer

DECLARE  Number int   1  SELECT RIGHT  0   CONVERT VARCHAR   Number   2  --OR SELECT RIGHT CONVERT VARCHAR  100    Number   2  GO

User · Answer

Another example   select  case when teamId  lt  10 then  0    cast teamId as char 1    else cast teamId as char 2   end       as  pretty id     from team

User · Answer

You can use T-SQL s built in format function   declare  number int    1 select format   number   0

User · Answer

select right   00  ltrim str   lt number gt     2

User · Answer

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 · Answer

try  right  0    convert varchar 2    number  2

User · Answer

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  lt  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   lt some int gt

User · Answer

SELECT RIGHT  0    CAST sortexport csv AS VARCHAR   2  FROM your table

User · Answer

Example for converting one digit number to two digit by adding 0     DECLARE  int INT   9          SELECT CASE WHEN  int  lt  10                     THEN FORMAT CAST  int AS INT   0                   ELSE                     FORMAT CAST  int AS INT   0                  END

User · Answer

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 · Answer

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

User · Answer

Try this  --Generate number from 2 to 90   with numcte as  select 2 as rn union all select rn 1 from numcte where rn lt 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 · Answer

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

User · Answer

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

User · Answer

here you go  select RIGHT REPLICATE  0   2    CAST 2 AS VARCHAR 2    2    should return 02

User · Answer

DECLARE  Number int   1  SELECT RIGHT  0   CONVERT VARCHAR   Number   2  --OR SELECT RIGHT CONVERT VARCHAR  100    Number   2  GO

User · Answer

SELECT replace str month DATEADD month  -1   2012-02-29     2         0

[tsql] T-SQL Format integer to 2-digit string

Examples related to tsql

Examples related to format

Examples related to casting