Padding

>>Script Language and Platform: SQL Server 2000
This script will pad a given string on the left or right of the column or string value.

--Parameter : Pass 'L' for Left padding and 'R' for right padding.

--Usage
select dbo.ALLPAD ('012233','*',10,'L')
--results
****012233

select dbo.ALLPAD ('012233','2',10,'R')
--results
0122332222

select dbo.ALLPAD ('43434','-',30,'L')
--results
-------------------------43434

select dbo.ALLPAD (Columnname,'-',30,'L') from tablename

Author: MAK

Create function dbo.ALLPAD (@text varchar(128),@PADtext char(1), @PADlimit int,@PADType char(1))
returns varchar(512)
as
begin
--Objective : To pad the given string on the left or right of the column or string value
--Developed by: MAK
--Date: Jul 23, 2004
declare @resultPAD varchar(512)
set @resultPAD =''

--Left Padding
If @PADtype ='L' 
begin
set @resultPAD =right(replicate(@PADtext,@PADlimit)+@text,@PADlimit)
end
--Right Padding

If @PADtype ='R' 
begin
set @resultPAD =left(@text+replicate(@PADtext,@PADlimit),@PADlimit)
end

--No Padding
If @PADtype not in ('R' , 'L' )
begin
set @resultPAD = NULL
end	

return @resultPAD

end


--Usage

/*
select dbo.ALLPAD ('012233','*',10,'L')
--results
****012233

select dbo.ALLPAD ('012233','2',10,'R')
--results
0122332222

select dbo.ALLPAD ('43434','-',30,'L')
--results
-------------------------43434
*/

Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, or fitness for a particular purpose... Disclaimer Continued

Back to Database Journal Home