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)
AS
BEGIN
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
END


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


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


Thanks
A Rahim Khan

Advertisements
  1. No trackbacks yet.

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

%d bloggers like this: