>>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
****012233select dbo.ALLPAD (‘012233′,’2′,10,’R’)
–results
0122332222select dbo.ALLPAD (‘43434′,’-‘,30,’L’)
–results
————————-43434select 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 PaddingIf @PADtype =’R’
begin
set @resultPAD =left(@text+replicate(@PADtext,@PADlimit),@PADlimit)
end–No Padding
If @PADtype not in (‘R’ , ‘L’ )
begin
set @resultPAD = NULL
endreturn @resultPAD
end
–Usage
/*
select dbo.ALLPAD (‘012233′,’*’,10,’L’)
–results
****012233select dbo.ALLPAD (‘012233′,’2′,10,’R’)
–results
0122332222select 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