SQL Tip #7 – Formatting numbers with leading zeros (Left Padding)

In order to format a number with leading zeros, one of the following solutions could be used:

Solution 1:

DECLARE @number INT
DECLARE @length INT
SET @number = 123
SET @length = 10

SELECT REPLACE (STR( @number, @length ), ' ' , '0' ) 
       AS NewNumber

Solution 2:

DECLARE @number INT
DECLARE @length INT
SET @number = 123
SET @length = 10

SELECT RIGHT('0000000000'+ 
       CONVERT(VARCHAR,@number),@length) 
       AS NewNumber 

Solution 3:

DECLARE @number INT
DECLARE @length INT
SET @number = 123
SET @length = 10

SELECT REPLICATE('0',@length - LEN(@number)) + 
       CONVERT(VARCHAR,@number) 
       AS NewNumber
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s