T-SQL Tip: Inserting Leading Characters to a String

Scenario: You have a fixed-length table column for which you want to add leading characters.
Example: In this scenario we have column "ID" which is a varchar field of length 10 and we want to add  leading zeros so that its values always have 10 characters.
--Create Development/Test Database
CREATE DATABASE [DevDB]
GO
--Use Database
USE [DevDB]
GO

--Create Table
CREATE TABLE [dbo].[TestID](
[ID] [varchar](10) NULL
) ON [PRIMARY]
GO

--Populate Table With Data
INSERT INTO dbo.TestID
VALUES
('1'),
('12'),
('123'),
('1234'),
('12345'),
('123456'),
('1234567'),
('12345678'),
('123456789')
GO

--Test the Transformation Logic (number 10 is the ID column size)
SELECT ID,RIGHT(('0000000000'+ID),10) as FilteredID
FROM dbo.TestID
GO

--Execute the Transformation Logic (number 10 is the ID column size)
UPDATE dbo.TestID
SET ID=RIGHT(('0000000000000'+ID),10)
GO

So, with a single UPDATE statement along with the use of the RIGHT string function you can easily add leading characters to a string.



--
My Latest Projects:

Labels: , ,