Pad length

,

SELECT dbo.[KDT_FN_PADLEN]('11','0',7,'Y') 
will result in
 000000011
 SELECT  dbo.[KDT_FN_PADLEN]('11','0',7,'Y','Y') 
 00000011
  
  Pad at Back 
  SELECT  dbo.[KDT_FN_PADLEN]('11','0',7,'N','Y') 
if exists (select 1 from sysobjects where name ='KDT_FN_PADLEN' and xtype ='Fn') 
drop Function [KDT_FN_PADLEN]

go 
create FUNCTION [dbo].[KDT_FN_PADLEN]
( 
	@Value AS VARCHAR(255),
	@CharsTobePaddedWith AS VARCHAR(10),
	@PadLenght AS INTEGER,
	@PadatFront AS CHAR(1),
	@UseFixedLength AS CHAR(1) = 'N'	
) 
/*
Auther:		Kraai
Purpose:	Pad String with Characters in front or Back , with Fixed lenght or not 
Use:
	
SELECT dbo.[KDT_FN_PADLEN]('11','0',7,'Y') 
will result in
 000000011
 SELECT  dbo.[KDT_FN_PADLEN]('11','0',7,'Y','Y') 
 00000011
  

  Pad at Back 
   SELECT  dbo.[KDT_FN_PADLEN]('11','0',7,'N','Y') 
 
 Quote od the Day: Never Judge a book by its Cover... Except if its a playboy.
 Website : www.jfkproductions.co.za
           www.kraaicomedy.com


*/


RETURNS VARCHAR(500)
AS
 BEGIN
 DECLARE @PaddedChar AS VARCHAR(1200)
 DECLARE @counter AS INTEGER 
 SET @PaddedChar =@Value
 SET @counter = 0
 IF @UseFixedLength = 'N' 
 BEGIN 
	 WHILE @counter < @PadLenght 
		BEGIN
			IF @PadatFront ='Y' 
			BEGIN
				SET @PaddedChar = @CharsTobePaddedWith + @PaddedChar
			END
			ELSE
			BEGIN
				SET @PaddedChar = @PaddedChar + @CharsTobePaddedWith 	
			END
			SET @counter = @counter + 1
		END 
END
ELSE IF  @UseFixedLength = 'Y'
BEGIN 
	
	 WHILE @counter < @PadLenght 
		BEGIN
			IF @PadatFront ='Y' 
			BEGIN
				SET @PaddedChar = @CharsTobePaddedWith + @PaddedChar
			END
			ELSE
			BEGIN
				SET @PaddedChar = @PaddedChar + @CharsTobePaddedWith 	
			END
			SET @counter = LEN(@PaddedChar)
		END 
END 
RETURN @PaddedChar

END

Rate

3 (1)

Share

Share

Rate

3 (1)