Re: STUFF command

From: Frank van Bortel <fvanbortel_at_netscape.net>
Date: Sun, 04 Apr 2004 16:12:56 +0200
Message-ID: <c4p50c$4fi$1@news1.tilbu1.nb.home.nl>

Dave Sisk wrote:

> Lookup the REPLACE function in the Oracle docs.  I believe this will do what
> you want.  (Btw, STUFF in SQLServer is a function also...:-)
> 
> HTH,
> Dave
> 
> <grille11_at_yahoo.com> wrote in message
> news:c4e6ad$k1n$1_at_reader1.imaginet.fr...
> 

>>Hello,
>>
>>I am trying to find the equivalent of the STUFF command from SQL (deletes

> 
> a
> 

>>specified length of characters and inserts another set of characters at a
>>specified starting point).
>>Would someone knows this?
>>thanks in advance
>>
>>
>>This example returns a character string created by deleting three

> 
> characters
> 

>>from the first string (abcdef) starting at position 2 (at b) and inserting
>>the second string at the deletion point.
>>
>>SELECT STUFF('abcdef', 2, 3, 'ijklmn')
>>GO
>>---------
>>aijklmnef
>>
>>
>>
>>
>>

> 
> 
> 

CREATE OR REPLACE FUNCTION STUFF(strbase IN VARCHAR2,position IN NUMBER,rmlength
IN NUMBER,strnew IN VARCHAR2)RETURN VARCHAR2   AS
    strresult VARCHAR2(4000);
strbase_length INTEGER;
  BEGIN
    strbase_length:=LENGTH(strbase);
IF( (position < 0) OR (rmlength < 0) OR (position >strbase_length))THEN strresult :=NULL;

        RETURN strresult;
      ELSE
        strresult

:=SUBSTR(strbase,1,position-1)||strnew||SUBSTR(strbase,position + rmlength); RETURN strresult;

      END IF;
  END;
/

dbo_at_O920.CSDB01.CS.NL> SELECT STUFF('abcdef', 2, 3, 'ijklmn') from dual;

STUFF('ABCDEF',2,3,'IJKLMN')

---

aijklmnef

-- 

Regards,
Frank van Bortel

Received on Sun Apr 04 2004 - 09:12:56 CDT