Most efficient T-SQL way to pad a varchar on the left to a certain length

Question

As compared to say   REPLICATE  padchar   len - LEN  str      str

User · Answer

I know this was originally asked back in 2008, but there are some new functions that were introduced with SQL Server 2012. The FORMAT function simplifies padding left with zeros nicely. It will also perform the conversion for you:

declare @n as int = 2
select FORMAT(@n, 'd10') as padWithZeros

Update:

I wanted to test the actual efficiency of the FORMAT function myself. I was quite surprised to find the efficiency was not very good compared to the original answer from AlexCuse. Although I find the FORMAT function cleaner, it is not very efficient in terms of execution time. The Tally table I used has 64,000 records. Kudos to Martin Smith for pointing out execution time efficiency.

SET STATISTICS TIME ON
select FORMAT(N, 'd10') as padWithZeros from Tally
SET STATISTICS TIME OFF

SQL Server Execution Times: CPU time = 2157 ms, elapsed time = 2696 ms.

SET STATISTICS TIME ON
select right('0000000000'+ rtrim(cast(N as varchar(5))), 10) from Tally
SET STATISTICS TIME OFF

SQL Server Execution Times:

CPU time = 31 ms, elapsed time = 235 ms.

User · Answer

To provide numerical values rounded to two decimal places but right-padded with zeros if required I have   DECLARE  value   20 1 SET  value   ROUND  value 2    100 PRINT LEFT CAST  value AS VARCHAR 20    LEN  value -2          RIGHT CAST  value AS VARCHAR 20   2    If anyone can think of a neater way  that would be appreciated - the above seems clumsy   Note  in this instance  I m using SQL Server to email reports in HTML format and so wish to format the information without involving an additional tool to parse the data

User · Answer

I m not sure that the method that you give is really inefficient  but an alternate way  as long as it doesn t have to be flexible in the length or padding character  would be  assuming that you want to pad it with  0  to 10 characters   DECLARE     pad characters VARCHAR 10   SET  pad characters    0000000000   SELECT RIGHT  pad characters    str  10

User · Answer

probably overkill  I often use this UDF   CREATE FUNCTION  dbo   f pad before   string VARCHAR 255    desired length INTEGER   pad character CHAR 1   RETURNS VARCHAR 255  AS   BEGIN  -- Prefix the required number of spaces to bulk up the string and then replace the spaces with the desired character  RETURN ltrim rtrim          CASE           WHEN LEN  string   lt   desired length             THEN REPLACE SPACE  desired length - LEN  string          pad character     string           ELSE  string         END            END   So that you can do things like   select dbo f pad before  aaa   10

User · Answer

I liked vnRocks solution  here it is in the form of a udf  create function PadLeft         String varchar 8000         NumChars int        PadChar char 1         returns varchar 8000  as begin     return stuff  String  1  0  replicate  PadChar   NumChars - len  String    end

User · Answer

How about this   replace  space 3 - len MyField     3 is the number of zeros to pad

User · Answer

Perhaps an over kill I have these UDFs to pad left and right  ALTER   Function  dbo   fsPadLeft   var varchar 200   padChar char 1   0   len int  returns varchar 300  as Begin  return replicate  PadChar  len-Len  var    var  end   and to right  ALTER function  dbo   fsPadRight   var varchar 200   padchar char 1   0    len int  returns varchar 201  as Begin  --select  padChar      len 200  var  hello    return   var replicate  PadChar  len-Len  var   end

User · Answer

Here is how I would normally pad a varchar  WHILE Len  String   lt  8 BEGIN     SELECT  String    0     String END

User · Answer

This is simply an inefficient use of SQL  no matter how you do it    perhaps something like   right  XXXXXXXXXXXX   rtrim  str    n    where X is your padding character and  n is the number of characters in the resulting string  assuming you need the padding because you are dealing with a fixed length      But as I said you should really avoid doing this in your database

User · Answer

select right replicate  padchar   len     str   len

User · Answer

In SQL Server 2005 and later you could create a CLR function to do this

User · Answer

I know this isn t adding much to the conversation at this point but I m running a file generation procedure and its going incredibly slow  I ve been using replicate and saw this trim method and figured I d give it a shot    You can see in my code where the switch between the two is in addition to the new  padding variable  and the limitation that now exists   I ran my procedure with the function in both states with the same results in execution time  So at least in SQLServer2016  I m not seeing any difference in efficiency that other found   Anyways  here s my UDF that I wrote years ago plus the changes today which is much the same as other s other than it has a LEFT RIGHT param option and some error checking   CREATE FUNCTION PadStringTrim         inputStr varchar 500         finalLength int        padChar varchar  1        padSide varchar 1    RETURNS VARCHAR 500   AS BEGIN     -- the point of this function is to avoid using replicate which is extremely slow in SQL Server     -- to get away from this though we now have a limitation of how much padding we can add  so I ve settled on a hundred character pad      DECLARE  padding VARCHAR  100     XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX      SET  padding   REPLACE  padding   X    padChar        SET  inputStr   RTRIM LTRIM  inputStr        IF LEN  inputStr   gt   finalLength          RETURN   ERROR   -- can search for   in the returned text       ELSE IF  finalLength  gt  LEN  inputStr           IF  padSide    L              SET  inputStr   RIGHT  padding    inputStr   finalLength              --SET  inputStr   REPLICATE  padChar   finalLength - LEN  inputStr      inputStr         ELSE IF  padSide    R              SET  inputStr   LEFT  inputStr    padding   finalLength              --SET  inputStr    inputStr   REPLICATE  padChar   finalLength - LEN  inputStr           -- if LEN  inputStr     finalLength we just return it      RETURN  inputStr  END  -- SELECT  dbo PadStringTrim  tblAccounts account  20         R    from tblAccounts -- SELECT  dbo PadStringTrim  tblAccounts account  20         L    from tblAccounts

User · Answer

I use this one  It allows you to determine the length you want the result to be as well as a default padding character if one is not provided  Of course you can customize the length of the input and output for whatever maximums you are running into                                                                      Author           Joey Morgan  Create date      November 1  2012  Description      Pads the string  MyStr with the character in                     PadChar so all results have the same length                                                                      CREATE FUNCTION  dbo   svfn AMS PAD STRING                      MyStr VARCHAR 25             LENGTH INT            PadChar CHAR 1    NULL           RETURNS VARCHAR 25   AS        BEGIN         SET  PadChar   ISNULL  PadChar   0            DECLARE  Result VARCHAR 25           SELECT              Result   RIGHT SUBSTRING REPLICATE  0    LENGTH   1                                          LENGTH   1  - LEN RTRIM  MyStr                                  RTRIM  MyStr    LENGTH           RETURN  Result        END   Your mileage may vary   -  Joey Morgan Programmer Analyst Principal I WellPoint Medicaid Business Unit

User · Answer

I have one function that lpad with x decimals  CREATE FUNCTION  dbo   LPAD DEC        -- Add the parameters for the function here      pad nvarchar MAX        string nvarchar MAX        length int       dec int   RETURNS nvarchar max  AS BEGIN     -- Declare the return variable here     DECLARE  resp nvarchar max   IF LEN  string   length BEGIN     IF CHARINDEX      string  gt 0     BEGIN         SELECT  resp   CASE SIGN  string              WHEN -1 THEN                 -- Nros negativos grandes con decimales                 concat  -  SUBSTRING replicate  pad  length  1  length-len  string   ltrim str abs  string   length  dec                ELSE                 -- Nros positivos grandes con decimales                 concat SUBSTRING replicate  pad  length  1  length-len  string   ltrim str  string  length  dec                                  END     END     ELSE     BEGIN         SELECT  resp   CASE SIGN  string              WHEN -1 THEN                 --Nros negativo grande sin decimales                 concat  -  SUBSTRING replicate  pad  length  1   length-3 -len  string   ltrim str abs  string   length  dec                ELSE                 -- Nros positivos grandes con decimales                 concat SUBSTRING replicate  pad  length  1  length-len  string   ltrim str  string  length  dec                                  END                          END END ELSE     IF CHARINDEX      string  gt 0     BEGIN         SELECT  resp  CASE SIGN  string              WHEN -1 THEN                 -- Nros negativos con decimales                 concat  -  SUBSTRING replicate  pad  length  1  length-len  string   ltrim str abs  string   length  dec                ELSE                 --Ntos positivos con decimales                 concat SUBSTRING replicate  pad  length  1  length-len  string   ltrim str abs  string   length  dec                 END     END     ELSE     BEGIN         SELECT  resp   CASE SIGN  string              WHEN -1 THEN                 -- Nros Negativos sin decimales                 concat  -  SUBSTRING replicate  pad  length-3  1   length-3 -len  string   ltrim str abs  string   length  dec                ELSE                 -- Nros Positivos sin decimales                 concat SUBSTRING replicate  pad  length  1   length-3 -len  string   ltrim str abs  string   length  dec                END     END RETURN  resp   END

User · Answer

I hope this helps someone    STUFF   character expression   start   length  character expression    select stuff  str  1  0  replicate  0    n - len  str

User · Answer

this is a simple way to pad left   REPLACE STR FACT HEAD FACT NO  x  0        y    Where x is the pad number and y is the pad character   sample   REPLACE STR FACT HEAD FACT NO  3  0        0

User · Answer

Here s my solution  which avoids truncated strings and uses plain ol  SQL   Thanks to  AlexCuse   Kevin and  Sklivvz  whose solutions are the foundation of this code    --  charToPadStringWith  is the character you want to pad the string with  declare  charToPadStringWith char 1     X    -- Generate a table of values to test with  declare  stringValues table  RowId int IDENTITY 1 1  NOT NULL PRIMARY KEY  StringValue varchar max  NULL   insert into  stringValues  StringValue  values  null                  A      ABCDE      1234567890     -- Generate a table to store testing results in  declare  testingResults table  RowId int IDENTITY 1 1  NOT NULL PRIMARY KEY  StringValue varchar max  NULL  PaddedStringValue varchar max  NULL    -- Get the length of the longest string  then pad all strings based on that length  declare  maxLengthOfPaddedString int    select MAX LEN StringValue   from  stringValues   declare  longestStringValue varchar max     select top 1  StringValue from  stringValues where LEN StringValue     maxLengthOfPaddedString   select   longestStringValue   longestStringValue    maxLengthOfPaddedString   maxLengthOfPaddedString   -- Loop through each of the test string values  apply padding to it  and store the results in   testingResults   while  1 1  begin     declare          stringValueRowId int           stringValue varchar max        -- Get the next row in the   stringLengths  table      select top 1   stringValueRowId   RowId   stringValue   StringValue     from  stringValues      where RowId  gt  isnull  stringValueRowId  0       order by RowId       if    ROWCOUNT   0           break       -- Here is where the padding magic happens      declare  paddedStringValue varchar max    RIGHT REPLICATE  charToPadStringWith   maxLengthOfPaddedString     stringValue   maxLengthOfPaddedString        -- Added to the list of results      insert into  testingResults  StringValue  PaddedStringValue  values   stringValue   paddedStringValue   end  -- Get all of the testing results  select   from  testingResults

User · Answer

Here is my solution   I can pad any character and it is fast    Went with simplicity    You can change variable size to meet your needs  Updated with a parameter to handle what to return if null   null will return a null if null CREATE OR ALTER FUNCTION code fnConvert PadLeft       in str nvarchar 1024        pad length int        pad char nchar 1               rtn null NVARCHAR 1024        RETURNS NVARCHAR 1024  AS BEGIN      DECLARE  rtn  NCHAR 1024             RETURN RIGHT REPLACE  rtn      pad char  ISNULL  in str  rtn null    pad length  END GO  CREATE OR ALTER FUNCTION code fnConvert PadRight       in str nvarchar 1024         pad length int        pad char nchar 1               rtn null NVARCHAR 1024        RETURNS NVARCHAR 1024  AS BEGIN      DECLARE  rtn  NCHAR 1024             RETURN LEFT ISNULL  in str  rtn null  REPLACE  rtn      pad char    pad length  END GO   -- Example SET STATISTICS time ON  SELECT code fnConvert PadLeft  88  10  0            code fnConvert PadLeft null 10  0            code fnConvert PadLeft null 10  0  null        code fnConvert PadRight  88  10  0            code fnConvert PadRight null 10  0           code fnConvert PadRight null 10  0  NULL    0000000088  0000000000  NULL    8800000000  0000000000  NULL

User · Answer

Several people gave versions of this   right  XXXXXXXXXXXX    str   n    be careful with that because it will truncate your actual data if it is longer than n

User · Answer

padstr   REPLICATE  padchar   len  -- this can be cached  done only once  SELECT RIGHT  padstr    str   len

[sql] Most efficient T-SQL way to pad a varchar on the left to a certain length?

Examples related to sql

Examples related to sql-server

Examples related to tsql