Home » Other » General » Wish Oracle had an inbuilt function "SPLIT" (Not yet in Oracle)
Wish Oracle had an inbuilt function "SPLIT" [message #590886] Tue, 23 July 2013 13:44 Go to next message
Lalit Kumar B
Messages: 2306
Registered: May 2013
Location: World Wide on the Web
Senior Member
Hi folks,

I wish Oracle comes up with an inbuilt function to split the string depending on the delimiter we pass as parameters.

Somewhat like the str2tbl function of Tom Kyte in his example -

CREATE OR REPLACE TYPE mytabletype AS TABLE OF VARCHAR2 (255);
 
CREATE OR REPLACE FUNCTION str2tbl (
   p_str     IN   VARCHAR2,
   p_delim   IN   VARCHAR2 DEFAULT '.'
)
   RETURN mytabletype
AS
   l_str    LONG        DEFAULT p_str || p_delim;
   l_n      NUMBER;
   l_data   mytabletype := mytabletype ();
BEGIN
   LOOP
      l_n := INSTR (l_str, p_delim);
      EXIT WHEN (NVL (l_n, 0) = 0);
      l_data.EXTEND;
      l_data (l_data.COUNT) := LTRIM (RTRIM (SUBSTR (l_str, 1, l_n - 1)));
      l_str := SUBSTR (l_str, l_n + LENGTH (p_delim));
   END LOOP;
 
   RETURN l_data;
END;
/


I have used the above Tom Kyte's code a lot of times shamelessly.

I agree that with the advent of regular expressions it has made the coders life much easier and REGEXPs works wonderfully, but, the workaround to achieve the result is sometimes more than what we expect initially while coding.

[Updated on: Tue, 23 July 2013 13:54]

Report message to a moderator

Re: Wish Oracle had an inbuilt function "SPLIT" [message #590893 is a reply to message #590886] Tue, 23 July 2013 14:14 Go to previous messageGo to next message
BlackSwan
Messages: 22781
Registered: January 2009
Senior Member
some, many, most folks adhere to Third Normal Form & avoid storing multiple values in a single column delimited by some separator character;
therefore never need to split strings to being with.
Re: Wish Oracle had an inbuilt function "SPLIT" [message #590894 is a reply to message #590893] Tue, 23 July 2013 14:32 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2306
Registered: May 2013
Location: World Wide on the Web
Senior Member
Blackswan-

normal forms are OK. I am thinking about projects/teams that work on raw data.

What about the data like IP address, where each field separated by a delimiter has a meaning. And it gets stored as raw data in a column. While fetching/processing we need to split them.

So, per the 3rd normal form, I cant create a look up table for IP adrreses of billion users globally.

I still think it is very useful.

[Updated on: Tue, 23 July 2013 14:39]

Report message to a moderator

Re: Wish Oracle had an inbuilt function "SPLIT" [message #590895 is a reply to message #590894] Tue, 23 July 2013 14:50 Go to previous messageGo to next message
BlackSwan
Messages: 22781
Registered: January 2009
Senior Member
>What about the data like IP address,
IP# are nothing more than 32-bit integers & should be stored as such.
dotted quad notation is for people; not computers & is a data presentation issue (similar to DATE datatype)
Re: Wish Oracle had an inbuilt function "SPLIT" [message #590900 is a reply to message #590894] Tue, 23 July 2013 15:05 Go to previous messageGo to next message
Michel Cadot
Messages: 59083
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I agree with BlackSwan, I wish there will never be such function provided by Oracle but I'm sure it will come.

Regards
Michel
Re: Wish Oracle had an inbuilt function "SPLIT" [message #590902 is a reply to message #590900] Tue, 23 July 2013 15:12 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2306
Registered: May 2013
Location: World Wide on the Web
Senior Member
Because Oracle is a corporation and user requirements means a lot for the business.
Re: Wish Oracle had an inbuilt function "SPLIT" [message #590908 is a reply to message #590902] Tue, 23 July 2013 19:21 Go to previous message
BlackSwan
Messages: 22781
Registered: January 2009
Senior Member
I share with you the basic law about holes.

When you find yourself in a hole, the first thing you should do is Stop Digging!
If you never place multiple values delimited by separator character into a single column; then there is NO need for any SPLIT function.
Previous Topic: Pls help me to out in this regard
Next Topic: Oracle V12.1 released
Goto Forum:
  


Current Time: Tue Sep 16 09:15:09 CDT 2014

Total time taken to generate the page: 0.25959 seconds