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?
 
Pawan KumarConnect With a Mentor Database 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

2
 
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

1
 
Larry Bristersr. DeveloperAuthor Commented:
Daniel
I have to build a very specific length output with 12-15 columns on each returned line
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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

0
 
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
0
 
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 ?
0
 
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 :)
0
 
Pawan KumarConnect With a Mentor Database 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)

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

0
 
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.
0
 
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.
0
 
Pawan KumarDatabase ExpertCommented:
Yes the length should be 1 for (@typeofpadding VARCHAR(1)). Point noted. Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.