Technical Article

Pad String

,

Pads a string on either with either leading or trailing characters. You specify the direction, the tolal string length and the character to pad with.

Error conditions return null

IF exists (SELECT * from dbo.sysobjects 
WHERE id = object_id(N'[dbo].[PAD]') 
AND OBJECTPROPERTY(id, N'IsScalarFunction') = 1)
DROP FUNCTION [dbo].[PAD]
GO


CREATE FUNCTION dbo.PAD ( @vcSourceStr varchar(8000),
  @iStrLensmallint, 
  @cPadCharchar(1),
  @cPadDirectionchar(1))
/**************************************************************************
DESCRIPTION:  Adds pad characters to the left or right of a string

PARAMETERS:
@vcSourceStr - The original string to be padded
@iStrLen- The length the string will be once padding is applied
@cPadChar- The character with which the string will be padded
@cPadDirection-- To which side of the string will the padding be applied 
(L = Left, R = right

RETURNS:
Source string with padding applied

REMARKS:  This is very useful for applying leading zeros when a numeric is converted to a fixed size string.  
Is also good for adding trailing whitespace characters.

USAGE:
SELECT ISNULL(dbo.PAD('1',2,'0','L'),'1') AS PaddedString

AUTHOR:Karen Gayda

DATE: 07/15/2005

MODIFICATION HISTORY:
WHODATEDESCRIPTION
----------------------------------------------------------------

***************************************************************************/RETURNS varchar(8000)
AS
BEGIN

-- can't pad a string that is bigger than the total size already
IF LEN(@vcSourceStr)  > @iStrLen
BEGIN
RETURN(NULL)
END
-- a varchar can't exceed 8000 byte nor can the length be less than 1 byte
IF  @iStrLen > 8000 OR @iStrLen < 1
BEGIN
RETURN(NULL)
END


DECLARE @vcPadString varchar(8000) 
DECLARE @vcResultString varchar(8000)
DECLARE @iPadChars int

-- Build the pad string
SET @iPadChars = @iStrLen - LEN(@vcSourceStr) -- calculate how many pad chars are needed to fill
SET @vcPadString = REPLICATE(@cPadChar,@iPadChars)

--Create the result string with pad string appended to left or right of string
SET @vcResultString = CASE 
WHEN UPPER(@cPadDirection) = 'L' THEN  @vcPadString + @vcSourceStr
WHEN UPPER(@cPadDirection) = 'R' THEN  @vcSourceStr + @vcPadString
ELSE NULL -- error, invalid direction
END 


--Send it back with padding applied per caller specifications
RETURN (@vcResultString)
END

Rate

5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (2)

You rated this post out of 5. Change rating