T-SQL Tip: Inserting Leading Characters to a String

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:

Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server and Software Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Artemakis's official website can be found at aartemiou.com.