Technical Article

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)