Technical Article

SUBSTR - SUBSTRING function extension

SUBSTR returns a portion of char, beginning at character @StartPos, @Length characters long.

If @StartPos is 0, it is treated as 1.

If @StartPos is positive, Oracle counts from the beginning of char to find the first character.

If @StartPos is negative, Oracle counts backwards from the end of char.

CREATE  Function SUBSTR( @inString VARCHAR(8000), 
@StartPos INTEGER,
@Length   INTEGER
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE 
@v_retStr VARCHAR(8000)

IF @startPos = 0 
SET @v_retStr =  SUBSTRING(@inString,1,@length)
ELSE IF @startPos > 0 
               SET @v_retStr = SUBSTRING(@inString,@startPos,@length)
           ELSE
    SET @v_retStr = SUBSTRING(REVERSE(SUBSTRING(REVERSE(@inString),1,ABS(@StartPos))),1,@Length)
RETURN @v_retStr  
END

Rate

1 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

1 (1)

You rated this post out of 5. Change rating