Home » Other » General » stuff() equialent function in oracle
stuff() equialent function in oracle [message #231214] Mon, 16 April 2007 01:02 Go to next message
sunsanvin
Messages: 60
Registered: April 2006
Location: Hyderabad
Member

Dear Experts,
Can you please tell me the Stuff() function equialent function in oracle?



thanks in advance
Re: stuff() equialent function in oracle [message #231223 is a reply to message #231214] Mon, 16 April 2007 01:22 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
I don't think there's a direct equivalent in Oracle, but if you'd play around with SUBSTR and concatenation you should be able to get what you want.

MHE
Re: stuff() equialent function in oracle [message #231227 is a reply to message #231214] Mon, 16 April 2007 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the purpose of the "stuff" function?

Regards
Michel
Re: stuff() equialent function in oracle [message #231234 is a reply to message #231227] Mon, 16 April 2007 01:33 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Michel Cadot wrote on Mon, 16 April 2007 08:25
What is the purpose of the "stuff" function?

Regards
Michel


*ahem*google*ahem*
Re: stuff() equialent function in oracle [message #231237 is a reply to message #231234] Mon, 16 April 2007 01:38 Go to previous messageGo to next message
sunsanvin
Messages: 60
Registered: April 2006
Location: Hyderabad
Member


The function works like this

SELECT STUFF('abcdef', 2, 3, 'ijklmn')
GO

Here is the result set:

---------
aijklmnef

(1 row(s) affected)

Re: stuff() equialent function in oracle [message #231241 is a reply to message #231237] Mon, 16 April 2007 01:47 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Like I expected, SUBSTR will do. I had a quick go at it:

My test script:
CREATE OR REPLACE FUNCTION stuff (
   char_expr1   IN   VARCHAR2
 , startpos     IN   NUMBER
 , strlen       IN   NUMBER
 , char_expr2   IN   VARCHAR2
)
   RETURN VARCHAR2 DETERMINISTIC
AS
   v_return   VARCHAR2 (4000);
BEGIN
   IF LEAST (NVL (startpos, 0), NVL (strlen, 0)) >= 0
   THEN
      v_return    :=
            SUBSTR (char_expr1, 1, startpos - 1)
         || char_expr2
         || SUBSTR (char_expr1, startpos + strlen, LENGTH (char_expr1));
   END IF;

   RETURN v_return;
END stuff;
/
sho err

col mystuff format A25

SELECT stuff('HelloWorld',6,0,' ') mystuff
     , 'Hello World'               expected
FROM   dual
/

SELECT stuff('HelloWorld',6,4,NULL) mystuff
     , 'Hellod'                     expected
FROM   dual
/

SELECT stuff('abcdef', 2, 3, 'ijklmn') mystuff
     , 'aijklmnef'                    expected
FROM dual
/

DROP FUNCTION stuff
/


My run:
SQL> @orafaq

Function created.

No errors.

MYSTUFF                   EXPECTED
------------------------- -----------
Hello World               Hello World


MYSTUFF                   EXPECT
------------------------- ------
Hellod                    Hellod


MYSTUFF                   EXPECTED
------------------------- ---------
aijklmnef                 aijklmnef


Function dropped.

SQL>


MHE
Re: stuff() equialent function in oracle [message #231242 is a reply to message #231234] Mon, 16 April 2007 01:48 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Frank, of course I know how to find what is the purpose of stuff. Wink
It was just a way to tell to OP that when he post something that is not Oracle to also post either a definition or an url to this one.
Maybe, I should be more direct.

Regards
Michel
Previous Topic: software needed
Next Topic: major differences between oracle 8i/9i/10g
Goto Forum:
  


Current Time: Thu Mar 28 11:14:23 CDT 2024