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),
			  @iStrLen		smallint, 
			  @cPadChar		char(1),
			  @cPadDirection	char(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:
	WHO		DATE		DESCRIPTION
	---		----------	---------------------------------------------------

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

Share

Share

Rate

5 (2)