MS SQL Server function pad right

Hi,

Take a look at the approach below. It's not a function, however, do you really need a scalar function?

declare @output_length int, @type_of_padding nvarchar(1),@side_of_padding bit;
select @output_length=19
, @type_of_padding='9' 
, @side_of_padding=1 --right

SELECT name
, IIF(LEN(name)=@output_length
	,name
	,IIF(LEN(name)>@output_length
		, SUBSTRING(name,1,@output_length)
		,IIF(@side_of_padding=0
			, REPLICATE(@type_of_padding,@output_length-LEN(name))+name
			, name+REPLICATE(@type_of_padding,@output_length-LEN(name))
			)
		)
	) AS padded
from sys.objects

Select allOpen in new window

Daniel
I have to build a very specific length output with 12-15 columns on each returned line

Ok then

CREATE FUNCTION dbo.fn_cust_padding(@string varchar(max),@output_length int,@type_of_padding char(1),@side_of_padding bit)
RETURNS VARCHAR(max)
BEGIN
RETURN(
SELECT IIF(LEN(@string)=@output_length
	,@string
	,IIF(LEN(@string)>@output_length
		, SUBSTRING(@string,1,@output_length)
		,IIF(@side_of_padding=0
			, REPLICATE(@type_of_padding,@output_length-LEN(@string))+@string
			, @string+REPLICATE(@type_of_padding,@output_length-LEN(@string))
			)
		)
	) AS padded)
END;
GO

--test
declare @output_length int, @type_of_padding nvarchar(1),@side_of_padding bit;
select @output_length=19
, @type_of_padding='9' 
, @side_of_padding=1 --right
select name, dbo.fn_cust_padding(name,@output_length,@type_of_padding,@side_of_padding) as padded from sys.objects;

Select allOpen in new window

In case you have only one table in your long select statement I'd encourage you to consider creating inline TVF, it'll perform better than scalar.
Scalar's are bad :)

https://sqlblogcasts.com/blogs/simons/archive/2008/11/03/TSQL-Scalar-functions-are-evil-.aspx

Select allOpen in new window

If you put L in above version it return incorrect results, 30 times @TypePadding :)
Word of coment to my code, I decided to build something like Oracle's (L/R)PAD function.

Declare @string varchar(100) = 'lrbrister', @totalLength INT = 30, @TypePadding varchar(10) = '9', @SidePadding varchar(1) = 'L'
Select dbo.mypad(@string,@totalLength, @TypePadding, @SidePadding) as outstring

outstring
-------------------------------------------------------
999999999999999999999999999999

Select allOpen in new window

Regards,
Daniel

Hi Daniel
What and whr r u checking? I am getting correct results-

Declare @string varchar(100) = 'lrbrister', @totalLength INT = 30, @TypePadding varchar(10) = '9',
@SidePadding varchar(1) = 'L'
Select dbo.mypad(@string,@totalLength, @TypePadding, @SidePadding) as outstring
------------------------*/
outstring
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
999999999999999999999lrbrister

Did u check the trial 2 from my last comment ?

Please take a look:



Edit: Pawan, you've edited code, didn't notice, you must've done it when I was typing my post. It's fine after modification :)

Hey guys
I'll review tonight when I'm in front of my PV

Sure , pls let us know if you face any issues. Cheers

Thanks guys

welcome. glad to help always :)

Lol, never mind.

FYI, you may want to consider:

select dbo.mypad('lrbrister',30,'12345','L'),LEN(dbo.mypad('lrbrister',30,'12345','L')) --fill starts with 5, not 1
select dbo.mypad('lrbrister',5,'12345','L'),LEN(dbo.mypad('lrbrister',5,'12345','L')) --returns right-most bytes, not left-most

@Scott
My code is for padding a single value not for a batch.

That's not the way you wrote the function:
"@typeofpadding VARCHAR(5)"

And why?  Why restrict it to 1 char?  If one is going to create such a function, why not write it to handle multiple bytes?  It's easy enough to do.

Good point Scott.

SET NOCOUNT ON;
GO
IF OBJECT_ID('dbo.fn_cust_padding') IS NOT NULL
DROP FUNCTION dbo.fn_cust_padding;
GO
CREATE FUNCTION dbo.fn_cust_padding(@string varchar(max),@output_length int,@type_of_padding varchar(max),@side_of_padding bit)
RETURNS VARCHAR(max)
BEGIN
RETURN(
SELECT IIF(LEN(@string)=@output_length
	,@string
	,IIF(LEN(@string)>@output_length
		, SUBSTRING(@string,1,@output_length)
		,IIF(@side_of_padding=0
			, SUBSTRING(REPLICATE(@type_of_padding,ROUND((@output_length-LEN(@string))/LEN(@type_of_padding),0)+1),1,@output_length-LEN(@string))+@string
			, @string+SUBSTRING(REPLICATE(@type_of_padding,ROUND((@output_length-LEN(@string))/LEN(@type_of_padding),0)+1),1,@output_length-LEN(@string))
			)
		)
	) AS padded)
END;
GO

--TEST
select dbo.fn_cust_padding('lrbrister',30,'12345',0), LEN(dbo.fn_cust_padding('lrbrister',30,'12345',0));
--123451234512345123451lrbrister 30

select dbo.fn_cust_padding('lrbrister',5,'12345',1), LEN(dbo.fn_cust_padding('lrbrister',5,'12345',1));
--lrbri 5

Select allOpen in new window

>>And why?  Why restrict it to 1 char?  If one is going to create such a function, why not write it to handle multiple bytes?  It's easy enough to do.
I followed the requirement. In the below requirement we have given only one character. The code is working fine for this.
Declare @string varchar(100) = 'lrbrister', @totalLength = 30, @TypePadding = '9', @SidePadding = 'R'
Will change the code if required.

I followed the requirement.

Actually the initial q didn't explicitly specify a length for the fill pattern.

My main point was that you coded the function to accept up to 5 chars.  If the function only works correctly for 1 char, you shouldn't allow 5 chars to be passed in.

Yes the length should be 1 for (@typeofpadding VARCHAR(1)). Point noted. Thanks.