Padding in SQL Server 2005

SQL Server built-in function RIGHT can be used to format output with leading zeros or other character. It is used with or without REPLICATE function. However, RIGHT function expects string type argument not numeric. If you are in a situation that you need to use numeric value for padding, just convert it to string.

The following are the simplest use of RIGHT with and without REPLCATE.

SELECT RIGHT(‘000000’+ ‘1’, 6)
SELECT RIGHT(REPLICATE(‘000000’,6)+ ‘1’,6)

Here is the output.

This GetApplicantId function generates a formatted ApplicantId using @ApplicationDate, @PositionCode, @ApplicantIdentity.

CREATE FUNCTION GetApplicantId(@ApplicationDate datetime, @PositionCode int,@ApplicantIdentity int )
RETURNS varchar(20)
DECLARE @ApplicantId varchar(20);
SELECT @ApplicantId =
right(‘0000’ + CONVERT(varchar(4),@PositionCode),4) + CONVERT(varchar,@ApplicationDate,112)
SET @ApplicantId = @ApplicantId + ‘-‘
SELECT @ApplicantId = @ApplicantId + right(‘000000’ + CONVERT (varchar(7),@ApplicantIdentity),7)
RETURN @ApplicantId

You can use it in query or elsewhere in your application.

SELECT dbo.GetApplicantId(GETDATE(),1,1) as ApplicantId

A Rahim Khan

  1. No trackbacks yet.

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: