Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Padding function Expand / Collapse
Author
Message
Posted Sunday, March 9, 2008 7:57 PM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
Comments posted to this topic are about the item Padding function
Post #466454
Posted Sunday, April 20, 2008 10:16 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, September 25, 2014 10:35 PM
Points: 405, Visits: 559
I marginally prefer my version... Its a little shorter

Needs comments I know, but the parameters are exactly the same

CREATE FUNCTION fn_MyPadding (
@Text AS VARCHAR(500),
@Char AS CHAR(1),
@HowMany AS INT,
@LeftRight AS CHAR(1) = 'L')
RETURNS VARCHAR(500) AS
BEGIN
IF @Text IS NULL
SET @Text = @Char

DECLARE @Count SMALLINT
SET @Count = LEN(@Text)

IF @LeftRight = 'L'
SET @Text = REVERSE(@Text)

SET @Text = @Text + REPLICATE(@Char, @HowMany - @Count)

IF @LeftRight = 'L'
SET @Text = REVERSE(@Text)

RETURN (@Text)
END

Post #487702
Posted Monday, April 21, 2008 6:03 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 12, 2010 7:12 AM
Points: 69, Visits: 148
It should be fairly easy to also have a center function. Just the same as this function called twice. In the first call, do the length of the string as int(length/2) as a left pad. In the second call, do round(length/2, 0) as a right pad.

That way, you can have text centered for a header block or to fill out a check fancy or something.
Post #487875
Posted Monday, April 21, 2008 6:38 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 22, 2014 11:25 AM
Points: 1,127, Visits: 546
This is a variant of the version that I use. I've modified it to use the same arguments as the one given in the article. I liked the comment given about a center orientation. Perhaps I'll modify this to include that feature as well.

-- =============================================
-- Author: Aaron N. Cutshall
-- Create date: 21-Apr-2008
-- Description: Pad string with given character for length given
-- =============================================
ALTER FUNCTION fn_PadStr(
@OrigStr varchar(512),
@PadChar char(1) = ' ',
@Length int,
@Orientation char(1) = 'L')
RETURNS varchar(512)
AS
BEGIN
RETURN CASE WHEN @Orientation = 'R' THEN @OrigStr ELSE '' END +
REPLICATE(@PadChar, @Length - LEN(@OrigStr)) +
CASE WHEN @Orientation = 'L' THEN @OrigStr ELSE '' END
END




"...when ye are in the service of your fellow beings ye are only in the service of your God." -- Mosiah 2:17
Post #487902
Posted Monday, April 21, 2008 9:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 29, 2008 1:49 AM
Points: 12, Visits: 10
Yet another padding function. This will let you use strings instead of a single character.

/*
Example: Result:
SELECT dbo.fn_Padding('1', '0', 3, 'L') 001
SELECT dbo.fn_Padding('15', '0', 3, 'L') 015
SELECT dbo.fn_Padding('ABC', '*', 10, 'R') ABC*******
SELECT dbo.fn_Padding('ABC', '_°', 10, 'R') ABC_°_°_°_
SELECT dbo.fn_Padding('ABC', '_°', 10, 'L') °_°_°_°ABC
*/

CREATE FUNCTION dbo.fn_Padding
(
@Text VARCHAR(8000),
@PaddingChars VARCHAR(20),
@StringLength INT,
@Direction CHAR(1) = 'L'
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @PaddingString VARCHAR(8000)

-- Build string to use for padding
SET @PaddingString = REPLICATE(@PaddingChars, (@StringLength / LEN(@PaddingChars)) + 1)

-- Left padding
IF (@Direction = 'L')
SET @Text = RIGHT(@PaddingString + COALESCE(@Text, ''), @StringLength)

-- Right padding
IF (@Direction = 'R')
SET @Text = LEFT(COALESCE(@Text, '') + @PaddingString, @StringLength)

RETURN @Text
END

Post #488054
Posted Monday, April 21, 2008 11:05 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 8:43 AM
Points: 660, Visits: 42
Yet another padding function, pure genius at least in my opinion LOL :)
/* *************************************************************
* Author: Mike Garrison
* Create Date: 04-21-08
* Description: Pad a string to a given length to either side
**************************************************************** */

ALTER FUNCTION dbo.udf_PadString (
@Text VARCHAR(500) = '',
@Char VARCHAR(50),
@Length INT,
@Orientation CHAR(1) = 'L')

RETURNS VARCHAR(500)
AS
BEGIN
IF @Length < LEN (@Text)
SET @Length = LEN(@Text)
RETURN CASE WHEN @Text IS NULL THEN REPLICATE(@char, @length)
WHEN @Orientation = 'L' THEN RIGHT(REPLICATE(@char, @length) + @text, @Length)
WHEN @Orientation = 'R' THEN LEFT(@text + REPLICATE(@char, @length) , @Length)
ELSE ''
END
END


Post #488120
Posted Monday, April 21, 2008 11:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 15, 2010 12:45 PM
Points: 32, Visits: 29
My guess is that the original function is going to have serious scalability issues.
Post #488135
Posted Tuesday, April 29, 2008 11:30 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 11, 2009 8:20 AM
Points: 269, Visits: 1,093
Good article. I appreciate the way you put a parameter list at the top of the article as well as in the function itself. This made it easier to understand at first glance.

I also enjoyed the variations that others have provided.


Ian.

"If you are going through hell, keep going."
-- Winston Churchill
Post #492367
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse