Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!newsfeed.cwix.com!newsfeed.icl.net!newsfeed.fjserv.net!colt.net!pop-news-1.colt-telecom.nl!newsgate.cistron.nl!amsnews01.chello.com!newshub3.home.nl!newshub1.home.nl!home.nl!not-for-mail
From: Frank van Bortel <fvanbortel@netscape.net>
Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server,comp.databases.oracle.tools
Subject: Re: STUFF command
Date: Sun, 04 Apr 2004 16:12:56 +0200
Organization: @Home Benelux
Lines: 72
Message-ID: <c4p50c$4fi$1@news1.tilbu1.nb.home.nl>
References: <c4e6ad$k1n$1@reader1.imaginet.fr> <4jMac.38678$zy.375897@twister.southeast.rr.com>
Reply-To: fvanbortel@netscape.net
NNTP-Posting-Host: cc28855-a.hnglo1.ov.home.nl
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Trace: news1.tilbu1.nb.home.nl 1081087821 4594 217.121.193.81 (4 Apr 2004 14:10:21 GMT)
X-Complaints-To: abuse@home.nl
NNTP-Posting-Date: Sun, 4 Apr 2004 14:10:21 +0000 (UTC)
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax)
X-Accept-Language: en-us, en
In-Reply-To: <4jMac.38678$zy.375897@twister.southeast.rr.com>
Xref: newssvr20.news.prodigy.com comp.databases.oracle.misc:137400 comp.databases.oracle.server:258885 comp.databases.oracle.tools:85292

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@yahoo.com> wrote in message
> news:c4e6ad$k1n$1@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@O920.CSDB01.CS.NL> SELECT STUFF('abcdef', 2, 3, 'ijklmn') from dual;

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

Regards,
Frank van Bortel