LPAD and RPAD functions in SQL

Introduction

Padding is the process which adds the given data with the message to make confirm a certain length. It can be to the left side or right side of the message. Suppose your message is “rashed”, and you want to add padding to the left side and the length you need is 10, and you want to fill the length with “0”, then the result will be: “0000rashed”. In the same way, if you want right padding, the result will be “rashed0000”.

Most of the time we need to do padding in SQL Server, but the LPAD and RPAD functions are not built-in in SQL Server.

T-SQL Code to LPAD and RPAD

Execute these queries for LPAD and RPAD functions.

/*
 ==========================================================
 Author:Md. Humayun Rashed: rashed_2k3@yahoo.co.in
 Create date: 15/03/2012
 Description:This function work for Left padding. there are 3 parameters. the first parameter 
			is for which value you want to padding, 
			2nd parameter is how many length for padding and 3rd parameter is 
			which value will used to pad 
 ==========================================================
*/
alter function [dbo].[LPAD]
(
	@pad_value varchar(500),
	@pad_length int,
	@pad_with varchar(10)
)
returns varchar(5000)
as
BEGIN
	Declare @value_result varchar(5000)
	select @value_result= replace(str(@pad_value,@pad_length),' ',@pad_with) 
	return @value_result
END

/*
 ==========================================================
 Author:		Md. Humayun Rashed: rashed_2k3@yahoo.co.in
 Create date:	15/03/2012
 Description:	This function work for Right padding. there are 3 parameters. the first parameter is 
				for which value you want to padding, 
				2nd parameter is how many length for padding and 3rd parameter 
				is which value will used to pad 
 ==========================================================
*/
ALTER function [dbo].[RPAD]
(
	@pad_value varchar(500),
	@pad_length int,
	@pad_with varchar(10)
)
returns varchar(5000)
as
BEGIN
	Declare @valueResult varchar(5000)
	select @valueResult=@pad_value+replace(replace(str(@pad_value,@pad_length),' ',@pad_with),@pad_value,'')
	return @valueResult
END

History

• 15-March-2012