Pad a string with leading zeros so it s 3 characters long in SQL Server 2008

Question

I have a string that is up to 3 characters long when it s first created in SQL Server 2008 R2   I would like to pad it with leading zeros  so if its original value was  1  then the new value would be  001   Or if its original value was  23  the new value is  023   Or if its original value is  124  then new value is the same as original value   I am using SQL Server 2008 R2  How would I do this using T-SQL

User · Answer

Here s a more general technique for left-padding to any desired width   declare  x     int       123 -- value to be padded declare  width int       25  -- desired width declare  pad   char 1     0  -- pad character  select right justified   replicate                              pad                               width-len convert varchar 100   x                                                         convert varchar 100   x    However  if you re dealing with negative values  and padding with leading zeroes  neither this  nor other suggested technique will work  You ll get something that looks like this   00-123    Probably not what you wanted   So  hellip  you ll have to jump through some additional hoops Here s one approach that will properly format negative numbers   declare  x     float     -1 234 declare  width int       20 declare  pad   char 1     0   select right justified   stuff           convert varchar 99   x                               -- source string  converted from numeric value           case when  x  lt  0 then 2 else 1 end                   -- insert position          0                                                    -- count of characters to remove from source string          replicate  pad  width-len convert varchar 99   x     -- text to be inserted              One should note that the convert   calls should specify an  n varchar of sufficient length to hold the converted result with truncation

User · Answer

I created this function which caters for bigint and one leading zero or other single character  max 20 chars returned  and allows for length of results less than length of input number   create FUNCTION fnPadNum      Num BIGINT --Number to be padded   sLen BIGINT --Total length of results    PadChar varchar 1     RETURNS VARCHAR 20    AS   --Pads bigint with leading 0 s             --Sample    select dbo fnPadNum 201 5  0    returns  00201              --Sample    select dbo fnPadNum 201 5       returns    201              --Sample    select dbo fnPadNum 201 5       returns    201     BEGIN      DECLARE  Results VARCHAR 20       SELECT  Results   CASE       WHEN  sLen  gt   len ISNULL  Num  0        THEN replicate  PadChar   sLen - len  Num     CAST ISNULL  Num  0  AS VARCHAR       ELSE CAST ISNULL  Num  0  AS VARCHAR       END       RETURN  Results      END      GO       --Usage        SELECT dbo fnPadNum 201  5  0         SELECT dbo fnPadNum 201  5            SELECT dbo fnPadNum 201  5

User · Answer

If the field is already a string  this will work   SELECT RIGHT  000  ISNULL field     3    If you want nulls to show as  000   It might be an integer -- then you would want    SELECT RIGHT  000  CAST field AS VARCHAR 3   3       As required by the question this answer only works if the length  lt   3  if you want something larger you need to change the string constant and the two integer constants to the width needed   eg  0000  and VARCHAR 4   4

User · Answer

Use this function which suits every situation   CREATE FUNCTION dbo fnNumPadLeft   input INT   pad tinyint  RETURNS VARCHAR 250  AS BEGIN     DECLARE  NumStr VARCHAR 250       SET  NumStr   LTRIM  input       IF  pad  gt  LEN  NumStr           SET  NumStr   REPLICATE  0    Pad - LEN  NumStr      NumStr       RETURN  NumStr  END   Sample output  SELECT  dbo   fnNumPadLeft   2016 10  -- returns 0000002016 SELECT  dbo   fnNumPadLeft   2016 5  -- returns 02016 SELECT  dbo   fnNumPadLeft   2016 2  -- returns 2016 SELECT  dbo   fnNumPadLeft   2016 0  -- returns 2016

User · Answer

The safe method   SELECT REPLACE STR n 3       0     This has the advantage of returning the string       for n  lt  0 or n   999  which is a nice and obvious indicator of out-of-bounds input  The other methods listed here will fail silently by truncating the input to a 3-character substring

User · Answer

I know this is an old ticket but I just thought I d share this  I found this code which provides a solution  Not sure if it works on all versions of MSSQL  I have MSSQL 2016  declare  value as nvarchar 50    23 select REPLACE STR CAST  value AS INT    1 4   SPACE 1    0   as Leadingzero  This returns  quot 0023 quot   The 4 in the STR function is the total length  including the value  For example  4  23 and 123 will all have 4 in STR and the correct amount of zeros will be added  You can increase or decrease it  No need to get the length on the 23  Edit  I see it s the same as the post by  Anon

User · Answer

Wrote this because I had requirements for up to a specific length  9   Pads the left with the  pattern ONLY when the input needs padding  Should always return length defined in  pattern   declare  charInput as char 50     input   --always handle NULL    set  charInput   isnull  charInput      declare  actualLength as int   len  charInput   declare  pattern as char 50     123456789  declare  prefLength as int   len  pattern   if  prefLength  gt   actualLength     select Left Left  pattern   prefLength- actualLength     charInput   prefLength  else     select  charInput   Returns 1234input

User · Answer

I had similar problem with integer column as input when I needed fixed sized varchar  or string  output  For instance  1 to  01   12 to  12   This code works   SELECT RIGHT CONCAT  00  field  text  2    If the input is also a column of varchar  you can avoid the casting part

User · Answer

Try this with fixed length   select right  000000   123  5   select REPLICATE  0   5 - LEN 123      123

User · Answer

I have always found the following method to be very helpful   REPLICATE  0   5 - LEN Job Number     CAST Job Number AS varchar  as  NumberFull

User · Answer

For integers you can use implicit conversion from int to varchar   SELECT RIGHT 1000   field  3

User · Answer

For a more dynamic approach try this     declare  val varchar 5  declare  maxSpaces int set  maxSpaces   3 set  val    3  select concat REPLICATE  0   maxSpaces-len  val    val

User · Answer

Simple is that  Like   DECLARE  DUENO BIGINT SET  DUENO 5  SELECT  ND  STUFF  000000  6-LEN RTRIM  DueNo   1 LEN RTRIM  DueNo   RTRIM  DueNo   DUENO

User · Answer

I came here specifically to work out how I could convert my timezoneoffset to a timezone string for converting dates to DATETIMEOFFSET in SQL Server 2008  Gross  but necessary   So I need 1 method that will cope with negative and positive numbers  formatting them to two characters with a leading zero if needed  Anons answer got me close  but negative timezone values would come out as 0-5 rather than the required -05  So with a bit of a tweak on his answer  this works for all timezone hour conversions  DECLARE  n INT   13 -- Works with -13  -5  0  5  etc SELECT CASE      WHEN  n  lt  0 THEN  -    REPLACE STR  n   -1  2       0        ELSE       REPLACE STR  n 2       0   END     00

User · Answer

For those wanting to update their existing data here is the query   update SomeEventTable set eventTime RIGHT  00000  ISNULL eventTime      5

User · Answer

Here is a variant of Hogan s answer which I use in SQL Server Express 2012   SELECT RIGHT CONCAT  000   field   3    Instead of worrying if the field is a string or not  I just CONCAT it  since it ll output a string anyway  Additionally if the field can be a NULL  using ISNULL might be required to avoid function getting NULL results   SELECT RIGHT CONCAT  000   ISNULL field       3

User · Answer

Although the question was for SQL Server 2008 R2  in case someone is reading this with version 2012 and above  since then it became much easier by the use of FORMAT   You can either pass a standard numeric format string or a custom numeric format string as the format argument  thank Vadim Ovchinnikov for this hint    For this question for example a code like  DECLARE  myInt INT   1  -- One way using a standard numeric format string PRINT FORMAT  myInt  D3    -- Other way using a custom numeric format string PRINT FORMAT  myInt  00       outputs  001 001

[sql-server] Pad a string with leading zeros so it's 3 characters long in SQL Server 2008

Examples related to sql-server

Examples related to tsql