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...
Larry Bristersr. DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel_PLDB Expert/ArchitectCommented:
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

Larry Bristersr. DeveloperAuthor Commented:
Daniel
I have to build a very specific length output with 12-15 columns on each returned line
Daniel_PLDB Expert/ArchitectCommented:
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

Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Pawan KumarDatabase ExpertCommented:
Please try this..
simple and easy to understand.

Function code.

CREATE FUNCTION dbo.Mypad
(
	@string varchar(max),@totallength int,@typeofpadding VARCHAR(5),@sidepadding VARCHAR(1)
)
RETURNS VARCHAR(max)
BEGIN
RETURN
(
	
	SELECT 
		CASE WHEN @sidepadding = 'R' THEN 
			LEFT(CONCAT(@string,REPLICATE(@typeofpadding,@totallength)),@totallength)
		ELSE 
			RIGHT(CONCAT(REPLICATE(@typeofpadding,@totallength),@string),@totallength)
		END
)
END;
GO

Open in new window

TRIAL

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

Open in new window


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

(1 row(s) affected)

Open in new window



TRAIL 2

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

Open in new window


OUTPUT

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

(1 row(s) affected)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Daniel_PLDB Expert/ArchitectCommented:
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
Pawan KumarDatabase ExpertCommented:
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 ?
Daniel_PLDB Expert/ArchitectCommented:
Please take a look:

MyPad.jpg

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 :)
Pawan KumarDatabase ExpertCommented:
Ohh yes you are using old one. :) . I got this when i was testing and edited my comment... we need change below line-

RIGHT(CONCAT(REPLICATE(@typeofpadding,@totallength),@string),@totallength)

:)
Larry Bristersr. DeveloperAuthor Commented:
Hey guys
I'll review tonight when I'm in front of my PV
Pawan KumarDatabase ExpertCommented:
Sure , pls let us know if you face any issues. Cheers
Larry Bristersr. DeveloperAuthor Commented:
Thanks guys
Pawan KumarDatabase ExpertCommented:
welcome. glad to help always :)
Scott PletcherSenior DBACommented:
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
Pawan KumarDatabase ExpertCommented:
@Scott
My code is for padding a single value not for a batch.
Scott PletcherSenior DBACommented:
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.
Daniel_PLDB Expert/ArchitectCommented:
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

Pawan KumarDatabase ExpertCommented:
>>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.
Scott PletcherSenior DBACommented:
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.
Pawan KumarDatabase ExpertCommented:
Yes the length should be 1 for (@typeofpadding VARCHAR(1)). Point noted. Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.