Link to home
Start Free TrialLog in
Avatar of Larry Brister
Larry BristerFlag for United States of America

asked on

MS SQL Server function pad right

I need a function that I can pass in

varchar string
total length of output
type of padding
side of padding

In other words
Declare @string varchar(100) = 'lrbrister', @totalLength = 30, @TypePadding = '9', @SidePadding = 'R'

Then I could do the select...

Select dbo.mypad(@string,@totalLength, @TypePadding, @SidePadding) as outstring

And this would come back...

outstring
lrbrister999999999999999999999

If I passed in left 9's would be to left of course

etc...
Avatar of Daniel_PL
Daniel_PL
Flag of Poland image

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

Open in new window

Avatar of Larry Brister

ASKER

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;

Open 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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

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

User generated image

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 :)
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 :)
Avatar of Scott Pletcher
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

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