PadLeft function in T-SQL

Question

I have the following table A   id ---- 1 2 12 123 1234   I need to left-pad the id values with zero s   id ---- 0001 0002 0012 0123 1234   How can I achieve this

User · Answer

declare  T table id int  insert into  T values  1    2    12    123    1234   select right  0000  convert varchar 4   id   4  from  T   Result  ---- 0001 0002 0012 0123 1234

User · Answer

If someone is still interested  I found this article on DATABASE GUIDE  Left Padding in SQL Server     3 LPAD   Equivalents  In short  there are 3 methods mentioned in that article  Let s say your id 12 and you need it to display as 0012   Method 1     Use the RIGHT   Function The first method uses the RIGHT   function to return only the rightmost part of the string  after adding some leading zeros   SELECT RIGHT  00     12   4    Result  0012  Method 2     Use a Combination of RIGHT   and REPLICATE   This method is almost the same as the previous method  with the only difference being that I simply replace the three zeros with the REPLICATE   function   SELECT RIGHT REPLICATE  0   2     12   4    Result  0012  Method 3     Use a Combination of REPLACE   and STR   This method comes from a completely different angle to the previous methods   SELECT REPLACE STR  12   4       0     Result  0012  Check out the article  there is more in depth analysis with examples

User · Answer

More efficient way is    Select id  LEN id  From TableA Order by 2 1   The result   id ---- 1 2 12 123 1234

User · Answer

This is what I normally use when I need to pad a value   SET  PaddedValue   REPLICATE  0    Length - LEN  OrigValue     CAST  OrigValue as VARCHAR

User · Answer

This works for strings  integers and numeric   SELECT CONCAT REPLICATE  0   4 - LEN id    id   Where 4 is desired length  Works for numbers with more than 4 digits  returns empty string on NULL value

User · Answer

Something fairly ODBC compliant if needed might be the following   select ifnull repeat  0   5 -  floor log10 FIELD NAME     1                  cast  FIELD as varchar 10     from TABLE NAME   This bases on the fact that the amount of digits for a base-10 number can be found by the integral component of its log  From this we can subtract it from the desired padding width  Repeat will return null for values under 1 so we need ifnull

User · Answer

Create Function        Create FUNCTION  dbo   PadLeft                   Text NVARCHAR MAX             Replace NVARCHAR MAX             Len INT         RETURNS NVARCHAR MAX  AS     BEGIN            DECLARE  var NVARCHAR MAX            SELECT  var   ISNULL LTRIM RTRIM  Text                   RETURN   RIGHT REPLICATE  Replace  Len    var   Len        END   Example   Select dbo PadLeft  123456   0  8

User · Answer

A simple example would be      DECLARE  number INTEGER     DECLARE  length INTEGER     DECLARE  char   NVARCHAR 10      SET  number   1     SET  length   5     SET  char    0       SELECT FORMAT  number  replicate  char   length

User · Answer

I believe this may be what your looking for   SELECT padded id   REPLACE STR id  4   SPACE 1    0     FROM tableA   or  SELECT REPLACE STR id  4   SPACE 1    0   AS  padded id   FROM tableA   I haven t tested the syntax on the 2nd example  I m not sure if that works 100  - it may require some tweaking - but it conveys the general idea of how to obtain your desired output   EDIT  To address concerns listed in the comments       pkr298 - Yes STR does only work on numbers    The OP s field is an ID    hence number only    Desolator - Of course that won t work    the First parameter is 6 characters long   You can do something like   SELECT REPLACE STR id   SELECT LEN MAX id     4 FROM tableA    SPACE 1    0   AS  padded id  FROM tableA   this should theoretically move the goal posts    as the number gets bigger it should ALWAYS work     regardless if its 1 or 123456789     So if your max value is 123456    you would see 0000123456 and if your min value is 1 you would see 0000000001

User · Answer

SQL Server now supports the FORMAT function starting from version 2012  so   SELECT FORMAT id   0000   FROM TableA   will do the trick    If your id or column is in a varchar and represents a number you convert first   SELECT FORMAT CONVERT INT id    0000   FROM TableA

User · Answer

My solution is not efficient but helped me in situation where the values  bank cheque numbers and wire transfer ref no   were stored as varchar where some entries had alpha numeric values with them and I had to pad if length is smaller than 6 chars   Thought to share if someone comes across same situation  declare  minlen int   6 declare  str varchar 20   set  str    123  select case when len  str   lt   minlen then REPLICATE  0   minlen-len  str    str else  str end --Ans  000123  set  str    1234  select case when len  str   lt   minlen then REPLICATE  0   minlen-len  str    str else  str end --Ans  001234  set  str    123456  select case when len  str   lt   minlen then REPLICATE  0   minlen-len  str    str else  str end --Ans  123456  set  str    123456789  select case when len  str   lt   minlen then REPLICATE  0   minlen-len  str    str else  str end --Ans  123456789  set  str    123456789  select case when len  str   lt   minlen then REPLICATE  0   minlen-len  str    str else  str end --Ans  123456789   set  str    NEFT 123456789  select case when len  str   lt   minlen then REPLICATE  0   minlen-len  str    str else  str end --Ans  NEFT 123456789

User · Answer

-- Please look into these   select FORMAT 1   d4    select FORMAT 2   d4    select FORMAT 12   d4    select FORMAT 123   d4    select FORMAT 1234   d4      -- I hope these would help you

User · Answer

I needed this in a function on SQL server and adjusted Patrick s answer a bit   declare  dossierId int   123 declare  padded id varchar 7    set  padded id   REPLACE                SPACE 7 - LEN  dossierId     convert varchar 7    dossierId                  SPACE 1                    0     SELECT  dossierId as   dossierId         SPACE LEN  dossierId     convert varchar 7          dossierId  as withSpaces         padded id as   padded id

User · Answer

I created a function   CREATE FUNCTION  dbo   fnPadLeft   int int   Length tinyint  RETURNS varchar 255   AS  BEGIN     DECLARE  strInt varchar 255       SET  strInt   CAST  int as varchar 255       RETURN  REPLICATE  0     Length - LEN  strInt       strInt   END    Use  select dbo fnPadLeft 123  10   Returns  0000000123

User · Answer

Try this   SELECT RIGHT REPLICATE  0  4  CAST Id AS VARCHAR 4   4  FROM  Table A

User · Answer

I created a function to do this  where you can specify the desired output character length   CREATE FUNCTION  dbo   udfLeadingZero             String VARCHAR MAX           Len INT   RETURNS VARCHAR MAX  BEGIN     SET  String   RIGHT REPLICATE  0   Len   String  Len  RETURN  String END GO   Example results

User · Answer

Old post  but maybe this helps someone out   To complete until it ends up with 4 non-blank characters   SELECT RIGHT   0000  COLUMNNAME  4  FROM TABLENAME    To complete until 10   SELECT RIGHT   0000000000  COLUMNNAME  10  FROM TABLENAME    In case the column is numeric  convert it to varchar first with such code   Select RIGHT  0000  Convert nvarchar 20   COLUMNNAME   4  From TABLENAME   And to complete until 10 with a numeric field   SELECT RIGHT   0000000000  Convert nvarchar 20   COLUMNNAME   10  FROM TABLENAME

[string] PadLeft function in T-SQL

Examples related to string

Examples related to tsql