Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2010
    Posts
    1

    Lightbulb Unanswered: Sybase Stuff function equivalent in oracle

    can someone help me in exploring code which is equivalent to the what Stuff function in does in sybase
    SyBooks Online

    stuff
    Description

    Returns the string formed by deleting a specified number of characters from one string and replacing them with another string.
    Syntax

    stuff(char_expr1 | uchar_expr1, start, length, char_expr2 | uchar_expr2)

    Parameters

    char_expr1

    is a character-type column name, variable, or constant expression of char, varchar, nchar, or nvarchar type.

    uchar_expr1

    is a character-type column name, variable, or constant expression of unichar or univarchar type.

    start

    specifies the character position at which to begin deleting characters.

    length

    specifies the number of characters to delete.

    char_expr2

    is another character-type column name, variable, or constant expression of char, varchar, nchar, or nvarchar type.

    uchar_expr2

    is another character-type column name, variable, or constant expression of unichar or univarchar type.

    Examples
    Example 1

    select stuff("abc", 2, 3, "xyz")

    ----
    axyz

    Example 2

    select stuff("abcdef", 2, 3, null)

    go
    ---
    aef

    Example 3

    select stuff("abcdef", 2, 3, "")

    ----
    a ef

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking Easy stuff

    It's so easy a caveman can do it:
    Code:
    CREATE OR REPLACE FUNCTION Stuff 
        (P_instr VARCHAR2, P_start NUMBER, P_num NUMBER, P_repl VARCHAR2)
        RETURN VARCHAR2
    IS
    BEGIN
        IF P_start < 1 OR P_start > LENGTH (P_instr)
        THEN
            Raise_application_error (-20001, '!ERROR: Invalid parameter(s).');
        END IF;
    
        RETURN SUBSTR (P_instr, 1, P_start-1)
            || P_repl
            || SUBSTR (P_instr, (P_start + P_num));
    END;
    /
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •