MS SQL Server function pad right

Larry Brister
Larry Brister used Ask the Experts™
on
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...
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
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. Developer

Author

Commented:
Daniel
I have to build a very specific length output with 12-15 columns on each returned line
Daniel_PLDB Expert/Architect
Top Expert 2011

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

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Database Expert
Awarded 2016
Top Expert 2016
Commented:
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

Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
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 Expert
Awarded 2016
Top Expert 2016

Commented:
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/Architect
Top Expert 2011

Commented:
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 Expert
Awarded 2016
Top Expert 2016
Commented:
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. Developer

Author

Commented:
Hey guys
I'll review tonight when I'm in front of my PV
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Sure , pls let us know if you face any issues. Cheers
Larry Bristersr. Developer

Author

Commented:
Thanks guys
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
welcome. glad to help always :)
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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 Expert
Awarded 2016
Top Expert 2016

Commented:
@Scott
My code is for padding a single value not for a batch.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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/Architect
Top Expert 2011

Commented:
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 Expert
Awarded 2016
Top Expert 2016

Commented:
>>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 DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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 Expert
Awarded 2016
Top Expert 2016

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial