Programming Articles on SQLNetHub

T-SQL Tip: Inserting Leading Characters to a String

by
This post, is actually a T-SQL tip, on how to easily insert leading characters to a string.

The Scenario for Inserting Leading Characters

Consider that  you have a fixed-length table column for which you want to add leading characters, so that all its records, values with the same number of characters.

How to Insert Leading Characters for the Above Scenario

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.

 

Learn essential SQL Server development tips! Enroll to our Online Course!

Check our online course titled “Essential SQL Server Development Tips for SQL Developers”
(special limited-time discount included in link).

Sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!

Essential SQL Server Development Tips for SQL Developers - Online Course
(Lifetime Access, Q&A, Certificate of Completion, downloadable resources and more!)

Learn More

 

Recommended Online Courses:

 

Did you find this article useful and interesting? Feel free to leave your comment!

If you enjoy my SQL Server administration tips and articles, I have something special just for you. It is one of my eBooksand it is called “Administering SQL Server“. Check it out!

Subscribe to our newsletter and stay up to date with our latest articles on SQL Server and related technologies!

Check out our latest software releases! All our software tools have free trial versions to download.

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

Views: 5,121