Click here to Skip to main content
Click here to Skip to main content

LPAD and RPAD functions in SQL

By , 15 Mar 2012
Rate this:
Please Sign up or sign in to vote.

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author


Comments and Discussions

 
SuggestionFunctions in Transact-SQL PinmemberMember 105061236-Jan-14 1:04 
GeneralMy vote of 5 PinmembersameeraLiyanage19-Dec-12 13:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140415.2 | Last Updated 15 Mar 2012
Article Copyright 2012 by Md. Humayun Rashed
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid