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]
CREATE TABLE [dbo].[TestID](
[ID] [varchar](10) NULL
) ON [PRIMARY]
–Populate Table With Data
INSERT INTO dbo.TestID
–Test the Transformation Logic (number 10 is the ID column size)
SELECT ID,RIGHT((‘0000000000’+ID),10) as FilteredID
–Execute the Transformation Logic (number 10 is the ID column size)
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:
- DBA Security Advisor: Secure your SQL Server instances against security risks.
- In-Memory OLTP Simulator: Easily benchmark SQL Server’s In-Memory OLTP Engine against your custom workload.
- Artemiou SQL Books: Download my latest free books on SQL Server.
- Artemiou Data Tools: See my latest software projects.