>>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