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
