How to pad a numeric type column with leading zeros?

Execute the following Microsoft SQL Server T-SQL example scripts to demonstrate SQL padding of numbers and strings with leading and trailing zeros as well as with other designated characters, and techniques for removing (trim) leading zeros.

– SQL Server leading zero – T-SQL padding numbers – lpad tsql – sql pad zero

SELECT ListPrice,

Padded=RIGHT(’0000000000′ + CONVERT(VARCHAR,ListPrice), 10)

FROM AdventureWorks2008.Production.Product WHERE ListPrice > 0.0

————

– T SQL pad leading zeros – transact sql leading zeros

SELECT ProductID, ReorderPoint = REPLACE(STR(ReorderPoint, 6), SPACE(1), ’0′)

FROM AdventureWorks2008.Production.Product

/* ProductID            ReorderPoint

1                 000750

2                 000750

3                 000600  */

————

– SQL Server leading zero – SQL leading zeros – sql server pad leading zero

USE AdventureWorks2008;

DECLARE @Number int = 789

SELECT RIGHT (’000000′+ CAST (@Number AS varchar), 6)

– 000789

————

USE AdventureWorks;

– SQL padding salary pay rate money data type with leading zeroes

– SQL left pad number – prefix number with zeros – sql server leading zero

– SQL convert number to text – cast number to varchar string

SELECT EmployeeID,

Rate,

PaddedRate = RIGHT(REPLICATE(’0′,8) + CAST(Rate AS VARCHAR(8)),8)

FROM   HumanResources.EmployeePayHistory
/* Partial results

EmployeeID        Rate              PaddedRate

1                 12.45             00012.45

2                 13.4615           00013.46

3                 43.2692           00043.27

*/

– SQL zero padding ListPrice money data type – t sql leading zero

– SQL left pad – T-SQL string concatenation – sql concat

– SQL convert number to string – pad numeric with zeros

SELECT ProductID,

ListPrice,

PaddedListPrice = RIGHT(REPLICATE(’0′, 8) + CAST(ListPrice AS VARCHAR(8)),8)

FROM Production.Product

/* Results sample

ProductID               ListPrice               PaddedListPrice

965                     742.35                  00742.35

*/

– SQL month leading zero – sql pad month number with zero

SELECT RIGHT(’0′ + convert(varchar(2), month(GetDate())), 2)

– 06
———-

– trim leading zeros – sql trim leading zeros – remove leading zeros sql

USE AdventureWorks2008;

DECLARE @num  varchar(32)= ’00091234560′

SELECT      right(@num, len(@num)+1 – patindex(‘%[^0]%’, @num))

– 91234560

————

– SQL pad numeric data type – SQL pad digits – transact sql leading zeros

– SQL pad with leading zeroes – append leading zeros – T-SQL top function

– SQL pad with trailing zeroes – MSSQL append trailing zeros

– SQL cast money to numeric – cast numeric to string – mssql newid function

SELECT TOP (5)

ProductName = Name,

ListPrice = RIGHT(REPLICATE(’0′, 10)

+ CAST(CAST(ListPrice AS NUMERIC(9,3)) AS VARCHAR) ,10)

FROM AdventureWorks.Production.Product

ORDER BY NEWID()

/* ProductName                      ListPrice

LL Mountain Frame – Black, 40       000249.790

HL Touring Frame – Yellow, 46       001003.910

Bike Wash – Dissolver               000007.950

Metal Sheet 7                       000000.000

HL Road Frame – Red, 56             001431.500

*/

———-

– PAD leading zeros function – sql server leading zeros – UDF

USE AdventureWorks2008;

GO

CREATE FUNCTION fnPadNumber

(@n      DECIMAL(26,2),

@length TINYINT)

RETURNS VARCHAR(32)

AS

BEGIN

RETURN ( replicate(’0′,@length – len(convert(VARCHAR(32),@n))) +

convert(VARCHAR(32),@n))

END

GO

SELECT dbo.fnPadNumber(1234567890.12,16)

– 0001234567890.12
————

– T SQL computed column zero padding – sql generate alphanumeric sequence

USE tempdb; — SQL Server 2008 T-SQL

CREATE TABLE Celebrity (

ID           INT    IDENTITY ( 1 , 1 )    PRIMARY KEY,

CelebrityID  AS ‘CEL’ + RIGHT(’0000′ + CAST( ID as varchar),5), — computed column

FirstName    VARCHAR(32),

LastName     VARCHAR(32),

ModifiedDate DATE    DEFAULT getdate())

GO

INSERT Celebrity

(FirstName,

LastName)

VALUES(‘Jennifer’,'Aniston’),

(‘Drew’,'Barrymore’),

(‘Diana’,'Princess of Wales’),

(‘Tom’,'Jones’),

(‘Lucille’,'Ball’),

(‘Frank’,'Sinatra’),

(‘Elvis’,'Presley’)

SELECT * FROM   Celebrity

GO

– CelebrityID is zero padded alphanumeric sequence

/*

ID    CelebrityID       FirstName  LastName          ModifiedDate

1     CEL00001          Jennifer    Aniston          2012-07-04

2     CEL00002          Drew        Barrymore        2012-07-04

3     CEL00003          Diana       Princess of Wales 2012-07-04

4     CEL00004          Tom         Jones             2012-07-04

5     CEL00005          Lucille     Ball              2012-07-04

6     CEL00006          Frank       Sinatra          2012-07-04

7     CEL00007          Elvis       Presley          2012-07-04

*/

– Cleanup demo

DROP TABLE Celebrity

GO
————

– SQL removing leading zeros when no spaces in string – trimming Leading Zeros

USE AdventureWorks2008;

DECLARE @NumberString varchar(16)=’000000246′

SELECT REPLACE(LTRIM(REPLACE(@NumberString, ’0′, ‘ ‘)), ‘ ‘, ’0′)

– 246

————

– SQL remove leading zeros – sql trim leading zeros – numeric test

DECLARE @StringWithLeadingZeros    VARCHAR(12) = ’000000654321′

SELECT CAST(CAST(@StringWithLeadingZeros AS INT) AS VARCHAR(10))

WHERE ISNUMERIC (@StringWithLeadingZeros)=1

– 654321

————

– LPAD & RPAD string scalar-valued user-defined functions (UDF)

USE AdventureWorks;

GO

– Left pad string function

CREATE FUNCTION LPAD

(@SourceString VARCHAR(MAX),

@FinalLength  INT,

@PadChar      CHAR(1))

RETURNS VARCHAR(MAX)

AS

BEGIN

RETURN

(SELECT Replicate(@PadChar,@FinalLength – Len(@SourceString)) + @SourceString)

END

GO

– T-SQL Test left padding

SELECT LeftPaddedString = dbo.LPAD(Cast(84856 AS VARCHAR),12,’0′)

GO

– 000000084856

– MSSQL right pad string function

CREATE FUNCTION RPAD

(@SourceString VARCHAR(MAX),

@FinalLength  INT,

@PadChar      CHAR(1))

RETURNS VARCHAR(MAX)

AS

BEGIN

RETURN

(SELECT @SourceString + Replicate(@PadChar,@FinalLength – Len(@SourceString)))

END

GO

– Test right padding

SELECT RightPaddedString = dbo.RPAD(Cast(84856 AS VARCHAR),12,’*')

GO

– 84856*******

———-

– Padding a money column with leading zeroes – sql convert leading zero

– SQL convert money data type to string

SELECT PaddedUnitPrice = RIGHT(replicate(’0′,20) +

convert(varchar,UnitPrice,1), 20)

FROM Northwind.dbo.Products

/* Partial results

PaddedUnitPrice

00000000000000018.00

00000000000000019.00

00000000000000010.00

00000000000000022.00

*/

/**************** Zero padding other numeric data ****************/

– SQL Server 2008 version featuring the LEFT function

– SQL convert integer to text – convert integer to varchar

USE AdventureWorks2008;

DECLARE @InputNumber int = 522, @OutputLength tinyint = 12

DECLARE @PaddedString char(12)

SET @PaddedString = LEFT( replicate( ’0′, @OutputLength ),

@OutputLength – len( @InputNumber ) ) + convert( varchar(12), @InputNumber)

SELECT PaddedNumber=@PaddedString

/* Result

PaddedNumber

000000000522

*/

– SQL format currency and pad with leading spaces

– SQL Server lpad to 9 characters

SELECT   TOP (3)  ProductName=Name,

Price=   CONVERT(char(9), ListPrice, 1)

FROM Production.Product

WHERE ListPrice > 0.0 ORDER BY Newid()

/*

ProductName                         Price

LL Touring Frame – Blue, 62           333.42

LL Road Seat Assembly                 133.34

Road-250 Red, 58                    2,443.35

*/

————

– Padding with zeroes in the middle of string

DECLARE @Number varchar(10)

SET @Number = ’99999′

PRINT ‘TRK’ + REPLICATE(’0′, 12 – LEN(@Number)) + @Number

SELECT [Zero Padding]=’TRK’ + REPLICATE(’0′, 12 – LEN(@Number)) + @Number

– Result: TRK000000099999

– Applying the STUFF string function for zero padding

– SQL convert integer data type to string

DECLARE @SerialNo int, @OutputSize tinyint

SET @OutputSize = 10

SET @SerialNo = 6543

SELECT PaddedSerialNo =  STUFF(replicate(’0′, @OutputSize),

@OutputSize – len(@SerialNo)+1, len(@SerialNo), convert(varchar(9),@SerialNo))

– Result: 0000006543

– SQL pad integer with 0-s

– SQL str function – numeric to character conversion

SELECT TOP 5

CAST(replace(str(ProductID,6),’ ‘,’0′) AS char(6)) AS ProdID

FROM AdventureWorks.Production.Product

ORDER BY Name

/* Results

ProdID

000001

000879

000712

000003

000002

*/

– SQL pad string with character

– SQL create function

– SQL user-defined function

CREATE FUNCTION dbo.fnLeftPadString (

@Input VARCHAR(255),

@PadChar CHAR(1),

@LengthToPad TINYINT

)

RETURNS VARCHAR(255) AS

BEGIN

DECLARE @InputLen TINYINT

SET @InputLen = LEN(@Input)

RETURN

CASE

WHEN @InputLen < @LengthToPad

THEN REPLICATE(@PadChar, @LengthToPad – @InputLen) + @Input

ELSE @Input

END — CASE

END — UDF

GO

– SQL pad string – left padding – SQL left pad with asterisk

– SQL check printing – SQL currency formatting

DECLARE @DollarAmount varchar(20)

SET @DollarAmount = ’234.40′

SELECT PaddedString=’$'+dbo.fnLeftPadString(@DollarAmount, ‘*’, 10)

GO

– Result: $****234.40

– SQL currency formatting with asterisk-fill

DECLARE @Amount MONEY

SET @Amount = ’3534.40′

SELECT CurrencyFormat = ‘$’ + REPLACE(Convert(char(12),@Amount,1),’ ‘,’*')

– $****3,534.40

SELECT      PaddedProductID =

dbo.fnLeftPadString (CONVERT(varchar, ProductID), ’0′, 6),

ProductName=Name,

ListPrice

FROM Production.Product

ORDER BY PaddedProductID

GO

/* Partial results

PaddedProductID         ProductName       ListPrice

000757                  Road-450 Red, 48  1457.99

000758                  Road-450 Red, 52  1457.99

000759                  Road-650 Red, 58  782.99

000760                  Road-650 Red, 60  782.99

000761                  Road-650 Red, 62  782.99

*/

———-

– Generating tracking numbers

– SQL pad zeroes

WITH cteSequence(SeqNo)

AS (SELECT 1

UNION ALL

SELECT SeqNo + 1

FROM   cteSequence

WHERE  SeqNo < 1000000)

SELECT TOP 100 CAST((‘TRK’ + REPLICATE(’0′,

7 – LEN(CAST(SeqNo AS VARCHAR(6)))) +

CAST(SeqNo AS VARCHAR(6))) AS VARCHAR(10)) AS TrackingNo

FROM   cteSequence

OPTION (MAXRECURSION 0)

GO

/* Partial results

TrackingNo

TRK0000001

TRK0000002

TRK0000003

TRK0000004

TRK0000005

TRK0000006

TRK0000007

*/

———-

– SQL server pad

– SQL str function

– SQL pad integer

– SQL left pad

SELECT TOP (4) StaffName=LastName+’, ‘+FirstName,

PaddedEmployeeID = REPLACE(STR(EmployeeID, 6, 0), ‘ ‘, ’0′)

FROM HumanResources.Employee e

INNER JOIN Person.Contact c

ON e.ContactID = c.ContactID

ORDER BY NEWID()

/* Results

StaffName         PaddedEmployeeID

Dyck, Shelley     000214

Hines, Michael    000039

Ford, Jeffrey     000015

Caron, Rob        000168

*/

– SQL asterisk padding

– SQL pad with asterisk

– SQL right pad

SELECT  TOP ( 2 * 2 )

AddressID

, City+REPLICATE(‘*’, 20-len(City)) AS City

, PostalCode

FROM AdventureWorks.Person.[Address]

WHERE LEN(City) <= 20

ORDER by NEWID()

GO

/*

AddressID   City                          PostalCode

13465       Imperial Beach******    91932

23217       Corvallis***********    97330

18548       Milwaukie***********    97222

24893       Goulburn************    2580

*/

————

————

– SQL left pad any size string Alpha with any length string Beta

————

– SQL user-defined function – UDF – scalar-valued string function

– T-SQL varchar(max)

USE AdventureWorks2008;

GO

CREATE FUNCTION fnMaxPad

(@SourceString  VARCHAR(MAX),

@PaddingString VARCHAR(MAX),

@OutputLength  INT)

RETURNS VARCHAR(MAX)

AS

BEGIN

DECLARE  @WorkString VARCHAR(MAX) =

ISNULL(REPLICATE(@PaddingString,@OutputLength – len(ISNULL(@SourceString,0))),

”) + @SourceString

RETURN RIGHT(@WorkString, @OutputLength)

END

GO

– Execute UDF

SELECT TOP ( 5 ) [SQL Padding Demo] =

dbo.fnMaxPad(CONVERT(VARCHAR,FLOOR(ListPrice)),’_/’,21)

FROM     Production.Product

ORDER BY NEWID()

GO

/* Results

SQL Padding Demo

_/_/_/_/_/_/_/2443.00

/_/_/_/_/_/_/_/_/0.00

/_/_/_/_/_/_/_/147.00

/_/_/_/_/_/_/_/_/0.00

_/_/_/_/_/_/_/1003.00

*/

————

– SQL left pad unicode string Alpha with any length unicode string Beta

– MSSQL pad international

– SQL user-defined function – UDF – scalar-value function

– T-SQL nvarchar(max)

USE AdventureWorks2008;

GO

CREATE FUNCTION fnMaxPadInternational

(@SourceString  NVARCHAR(MAX),

@PaddingString NVARCHAR(MAX),

@OutputLength  INT)

RETURNS NVARCHAR(MAX)

AS

BEGIN

DECLARE  @WorkString NVARCHAR(MAX) =

ISNULL(REPLICATE(@PaddingString,@OutputLength – len(ISNULL(@SourceString,0))),

”) + @SourceString

RETURN RIGHT(@WorkString, @OutputLength)

END

GO

– Execute UDF

SELECT TOP ( 5 ) [SQL Padding Demo] =

dbo.fnMaxPadInternational(LEFT(Description,6),’_/’,21)

FROM     Production.ProductDescription

ORDER BY NEWID()

GO

/* Results

SQL Padding Demo

/_/_/_/_/_/_/_/ล้อที่

/_/_/_/_/_/_/_/Roue d

/_/_/_/_/_/_/_/شوكة ط

/_/_/_/_/_/_/_/Each f

/_/_/_/_/_/_/_/Jeu de

*/

————

Reference from sqlusa.com