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_at_news1.tilbu1.nb.home.nl>
>>Hello,
>>
>>I am trying to find the equivalent of the STUFF command from SQL (deletes
>>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
>>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;
:=SUBSTR(strbase,1,position-1)||strnew||SUBSTR(strbase,position + rmlength); RETURN strresult;
aijklmnef
Date: Sun, 04 Apr 2004 16:12:56 +0200
Message-ID: <c4p50c$4fi$1_at_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 BortelReceived on Sun Apr 04 2004 - 16:12:56 CEST