Thursday, December 24, 2009

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

No comments:

Post a Comment

Ideal SQL Query For Handling Error & Transcation in MS SQL

BEGIN TRY BEGIN TRAN --put queries here COMMIT; END TRY BEGIN CATCH IF @@TRANCOUNT>0 BEGIN SELECT @@ERROR,ERRO...