Home » SQL & PL/SQL » SQL & PL/SQL » Function to split a string
Function to split a string [message #33545] Mon, 18 October 2004 10:58 Go to next message
rachna
Messages: 4
Registered: November 2000
Junior Member
Hi,

I have a string like 10.01.03.04.234 or 234.05.07.032.  Is there a simple function in Oracle that would give me

10
01
03
04
234 as seperate values in a select clause.

I would like to do a

Select function(10.01.03.04.234)
into var1, var2, var3, var4, var5
from ....

I can accomplish the same with substr/length/instrs in a procedure...but looking for a simple function, if any, instead of writing a proc/function....

Thanks in advance.
Rachna

 
Re: Function to split a string [message #33546 is a reply to message #33545] Mon, 18 October 2004 11:24 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
No, you'll need to split it out yourself.
Re: Function to split a string [message #33550 is a reply to message #33545] Mon, 18 October 2004 18:46 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7991
Registered: November 2002
Location: California, USA
Senior Member
Please see if you can use the list_element function in the example below for your purpose:

scott@ORA92> -- test data:
scott@ORA92> SELECT * FROM your_table
  2  /

STRING_COL
------------------------------
10.01.03.04.234
234.05.07.032


scott@ORA92> -- function:
scott@ORA92> CREATE OR REPLACE FUNCTION list_element
  2    (p_string    VARCHAR2,
  3  	p_element   INTEGER,
  4  	p_separator VARCHAR2)
  5    RETURN	    VARCHAR2
  6  AS
  7    v_string     VARCHAR2(32767);
  8  BEGIN
  9    v_string := p_string || p_separator;
 10    FOR i IN 1 .. p_element - 1 LOOP
 11  	 v_string := SUBSTR(v_string,INSTR(v_string,p_separator)+1);
 12    END LOOP;
 13    RETURN SUBSTR(v_string,1,INSTR(v_string,p_separator)-1);
 14  END list_element;
 15  /

Function created.


scott@ORA92> -- usage:
scott@ORA92> SET SERVEROUTPUT ON
scott@ORA92> DECLARE
  2    var1 VARCHAR2(5);
  3    var2 VARCHAR2(5);
  4    var3 VARCHAR2(5);
  5    var4 VARCHAR2(5);
  6    var5 VARCHAR2(5);
  7  BEGIN
  8    FOR rec IN (SELECT string_col FROM your_table) LOOP
  9  	 SELECT list_element (rec.string_col, 1, '.'),
 10  		list_element (rec.string_col, 2, '.'),
 11  		list_element (rec.string_col, 3, '.'),
 12  		list_element (rec.string_col, 4, '.'),
 13  		list_element (rec.string_col, 5, '.')
 14  	 INTO	var1, var2, var3, var4, var5
 15  	 FROM	DUAL;
 16  	 DBMS_OUTPUT.PUT_LINE ('string: ' || rec.string_col);
 17  	 DBMS_OUTPUT.PUT_LINE ('var1: ' || var1);
 18  	 DBMS_OUTPUT.PUT_LINE ('var2: ' || var2);
 19  	 DBMS_OUTPUT.PUT_LINE ('var3: ' || var3);
 20  	 DBMS_OUTPUT.PUT_LINE ('var4: ' || var4);
 21  	 DBMS_OUTPUT.PUT_LINE ('var5: ' || var5);
 22  	 DBMS_OUTPUT.PUT_LINE ('-------------------------------');
 23    END LOOP;
 24  END;
 25  /
string: 10.01.03.04.234
var1: 10
var2: 01
var3: 03
var4: 04
var5: 234
-------------------------------
string: 234.05.07.032
var1: 234
var2: 05
var3: 07
var4: 032
var5:
-------------------------------

PL/SQL procedure successfully completed.
Re: Function to split a string [message #129968 is a reply to message #33550] Wed, 27 July 2005 08:24 Go to previous messageGo to next message
anikanch
Messages: 4
Registered: July 2005
Junior Member
Does any one know to pass table or arary out this split function rather then assign to couple variables. Array or PL?SQL table type is efficient specially when we don't know the string length.

Thanks
Anitha
Re: Function to split a string [message #129975 is a reply to message #129968] Wed, 27 July 2005 09:05 Go to previous messageGo to next message
limer
Messages: 15
Registered: June 2005
Location: Madrid
Junior Member
OK

months ago I made a oracle package with string manipulation functions.

One of them was split.

this is the code if it could help

TYPE ARRAY_STR IS VARRAY(256) OF VARCHAR2(128);

FUNCTION SPLIT (
CADENA IN VARCHAR2, /* input string to split */
CORTE IN VARCHAR2, /* splitting char */
aRESULT OUT ARRAY_STR /* array with result */
) RETURN NUMBER
AS
B NUMBER;
CAD VARCHAR2(512);
TMP VARCHAR2(128);
nRESULT NUMBER;
lc NUMBER DEFAULT 0;
D NUMBER DEFAULT 0;
C VARCHAR2(128);
P NUMBER DEFAULT 0;
BEGIN
nResult := 0;
IF CADENA iS NULL THEN
-- NULL string
RETURN -1001;
ELSIF (LENGTH(CADENA) > 512 ) THEN
-- string too long
RETURN -1002;
END IF;
C := CORTE;
aRESULT := ARRAY_STR();
if (C = '' or C is NULL) Then
C := ',';
lc := 1;
else
lc := LENGTH(C);
End If;
-- number of elements = array dimension
D := (LENGTH(CADENA) - LENGTH(REPLACE(CADENA,C)))/ lc;
IF (D = 0) THEN
-- unable to find splitting str
Return -1000;
END IF;
-- array dimension
aRESULT.EXTEND(D + 1);
B := 1;
--
CAD := CADENA;
WHILE (true) LOOP
-- first cut
P := InStr(CAD, C);
if (P = 0) Then
--
aRESULT(B) := Substr(CAD,1,LENGTH(CAD));
B := B + 1;
Exit;
End If;
aRESULT(B) := Substr(CAD,1,P - 1);
B := B + 1;
CAD := SUBSTR(CAD,P + lc);
END LOOP;
RETURN 0;
END SPLIT;



Re: Function to split a string [message #130025 is a reply to message #129975] Wed, 27 July 2005 14:27 Go to previous messageGo to next message
anikanch
Messages: 4
Registered: July 2005
Junior Member
Thanks for your quick response. Could you also let me know how to make a call to execute from Stored procedure.

Re: Function to split a string [message #130194 is a reply to message #130025] Thu, 28 July 2005 12:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7991
Registered: November 2002
Location: California, USA
Senior Member
The following is a slight modification of Tom Kyte's str2tbl function to allow a choice of delimiter, followed by sample usage from sql and pl/sql.

-- type and function:
scott@ORA92> create or replace type myTableType as table of varchar2(255);
  2  /

Type created.

scott@ORA92> create or replace function str2tbl
  2  	 (p_str   in varchar2,
  3  	  p_delim in varchar2 default '.')
  4  	 return      myTableType
  5  as
  6  	 l_str	    long default p_str || p_delim;
  7  	 l_n	    number;
  8  	 l_data     myTableType := myTabletype();
  9  begin
 10  	 loop
 11  	     l_n := instr( l_str, p_delim );
 12  	     exit when (nvl(l_n,0) = 0);
 13  	     l_data.extend;
 14  	     l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
 15  	     l_str := substr( l_str, l_n+length(p_delim) );
 16  	 end loop;
 17  	 return l_data;
 18  end;
 19  /

Function created.


-- sql:
scott@ORA92> SELECT *
  2  FROM   TABLE (CAST (str2tbl ('10.01.03.04.234') AS mytabletype))
  3  /

COLUMN_VALUE
----------------------------------------------------------------------------------------------------
10
01
03
04
234


-- pl/sql:
scott@ORA92> DECLARE
  2    v_array mytabletype;
  3  BEGIN
  4    v_array := str2tbl ('10.01.03.04.234');
  5    FOR i IN 1 .. v_array.COUNT LOOP
  6  	 DBMS_OUTPUT.PUT_LINE (v_array(i));
  7    END LOOP;
  8  END;
  9  /
10
01
03
04
234

PL/SQL procedure successfully completed.

Re: Function to split a string [message #130372 is a reply to message #130194] Fri, 29 July 2005 10:40 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Requires 9i:
SELECT EXTRACTVALUE(xt.column_value,'e')
FROM   TABLE(XMLSEQUENCE
           ( EXTRACT
             ( XMLTYPE('<coll><e>' ||
                       REPLACE('10.01.03.04.234','.','</e><e>') ||
                       '</e></coll>')
             , '/coll/e') )) xt;

In PL/SQL you would of course use a variable instead of hardcoding '10.01.03.04.234'. You might even prefer to separate the XML conversion from the splitting SQL:
DECLARE
    v_csvlist VARCHAR2(100) := '10.01.03.04.234';
    v_xmllist XMLTYPE :=
        XMLTYPE('<coll><e>' || REPLACE(v_csvlist,'.','</e><e>') || '</e></coll>');
BEGIN
    FOR r IN (
        SELECT EXTRACTVALUE(xt.column_value,'e') AS element
        FROM   TABLE(XMLSEQUENCE(EXTRACT(v_xmllist,'/coll/e'))) xt
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE(r.element);
    END LOOP;
END;

[Updated on: Fri, 29 July 2005 10:47]

Report message to a moderator

Re: Function to split a string [message #130396 is a reply to message #130194] Fri, 29 July 2005 14:50 Go to previous messageGo to next message
anikanch
Messages: 4
Registered: July 2005
Junior Member
Thanks Barbara Boehmer. This is what I am looking and works like charm.
Re: Function to split a string [message #130397 is a reply to message #130372] Fri, 29 July 2005 14:52 Go to previous messageGo to next message
anikanch
Messages: 4
Registered: July 2005
Junior Member
What version does this work? I tried on oracle 8i but no luck. It could be ODBC driver on my machine though.

Thanks anyway Wiliam.

Re: Function to split a string [message #130407 is a reply to message #130397] Fri, 29 July 2005 16:15 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Quote:

Requires 9i:

Quote:

What version does this work? I tried on oracle 8i but no luck.

It works in all currently supported versions.
Re: Function to split a string [message #394075 is a reply to message #33545] Wed, 25 March 2009 11:14 Go to previous messageGo to next message
morafaqm
Messages: 12
Registered: March 2009
Location: Denver
Junior Member
You can find string split function, which returns PL/SQL table aka array at [spam]

[Updated on: Thu, 23 December 2010 12:59] by Moderator

Report message to a moderator

Re: Function to split a string [message #394078 is a reply to message #394075] Wed, 25 March 2009 11:32 Go to previous messageGo to next message
Michel Cadot
Messages: 59280
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does this add to Barbara's answer but:
1/ it is not a working example
2/ you can't use it in SQL
3/ you use this stupid "when others then null;" showing how bad is your programming
4/ you just try to workaround rule about spamming.

Regards
Michel
Re: Function to split a string [message #394081 is a reply to message #394078] Wed, 25 March 2009 11:52 Go to previous messageGo to next message
morafaqm
Messages: 12
Registered: March 2009
Location: Denver
Junior Member
Topic View
Re: Function to split a string Wed, 25 March 2009 10:32
Michel Cadot

What does this add to Barbara's answer but:
1/ it is not a working example - of course it is working.

2/ you can't use it in SQL - it is useless as part of the query, but will work in anonymous block. You just have to define v_type.

3/ you use this stupid "when others then null;" showing how bad is your programming - it rather showing that this piece of code doesn't have exceptions. Even you don't have any data supplied - it won't throw an exception. It just shows your style of programming - not to make sure that supplied data is correct.

4/ you just try to workaround rule about spamming. - it is a working example, which probably requires some adaptation to individual needs, but not within the code itseld. The difference between this piece of code and anything else published on this forum - is a flexible delimiter. It can be of any length.



Re: Function to split a string [message #394085 is a reply to message #394081] Wed, 25 March 2009 12:06 Go to previous messageGo to next message
Michel Cadot
Messages: 59280
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> declare
  2  
  3  myString Varchar2(2000):='a:sd:dfg:31456:dasd: :sdfsdf';
  4  
  5  Type v_type is Table of Varchar2(300) index by Binary_Integer;
  6  
  7  v v_type;
  8  
  9  
 10  Begin 
 11  
 12  v:=GetStrings(myString,':');
 13  
 14  End;
 15  
 16  /*-----------------------------------------------------------<---------*/
 17  
 18  Function GetStrings
 19  (p_items Varchar2,
 20  p_delimiter Varchar2)
 21  Return v_type
 22  
 23  
 24  is
 25  
 26  v_varTab_i v_type;
 27  tempString Varchar2(16000);
 28  i Binary_Integer:=1;
 29  i_s number;
 30  i_e number;
 31  i_sp number;
 32  i_ep number:=1;
 33  t_items Varchar2(16000);
 34  
 35  
 36  
 37  Begin
 38  
 39  t_items:=rtrim(p_items,p_delimiter);
 40  t_items:=t_items||p_delimiter;
 41  i_s :=1;
 42  tempString:=substr(t_items,i_s);
 43  i:=1;
 44  
 45  while i_ep > 0
 46  Loop
 47  
 48  i_ep:=instr(tempString,p_delimiter,i_s);
 49  
 50  
 51  if i_ep >0 then
 52  if i_ep-i_s = 0 then v_varTab_i(i):= NULL;
 53  else
 54  v_varTab_i(i):=substr(tempString,i_s,i_ep-i_s);
 55  end if;
 56  i:=i+1;
 57  tempString:=substr(tempString,i_ep+length(p_delimiter));
 58  end if;
 59  
 60  End Loop;
 61  
 62  Return v_varTab_i;
 63  
 64  Exception
 65  when others then
 66  NULL;
 67  
 68  End GetStrings; 
 69  /
Function GetStrings
*
ERROR at line 18:
ORA-06550: line 18, column 1:
PLS-00103: Encountered the symbol "FUNCTION"
ORA-06550: line 19, column 10:
PLS-00103: Encountered the symbol "VARCHAR2" when expecting one of the following:
. ( ) , * @ % & | = - + < / > at in is mod remainder not
range rem => .. <an exponent (**)> <> or != or ~= >= <= <>
and or like LIKE2_ LIKE4_ LIK
ORA-06550: line 26, column 1:
PLS-00103: Encountered the symbol "V_VARTAB_I" when expecting one of the following:
language
ORA-06550: line 68, column 15:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
end not pragma final instantiable order overriding static
member constructor map

Is this that you call a working example?

Regards
Michel
Re: Function to split a string [message #394086 is a reply to message #394085] Wed, 25 March 2009 12:19 Go to previous messageGo to next message
morafaqm
Messages: 12
Registered: March 2009
Location: Denver
Junior Member
I updated the script, not the function.

You will find it slightly differently wrapped at the same place:

[spam]

If SQL is only thing you know - it will work with no problems.

Thanks and best regards.

[Updated on: Thu, 23 December 2010 13:00] by Moderator

Report message to a moderator

Re: Function to split a string [message #394099 is a reply to message #394086] Wed, 25 March 2009 12:53 Go to previous messageGo to next message
Michel Cadot
Messages: 59280
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
My other points are still valid.
"when others then null;" is simply a bug...

Regards
Michel
Re: Function to split a string [message #394103 is a reply to message #394099] Wed, 25 March 2009 13:03 Go to previous messageGo to next message
morafaqm
Messages: 12
Registered: March 2009
Location: Denver
Junior Member
Actually, it is only 1 point left.
I firmly beleive that there is no perfect code ever exists.

Also this is an atomic size function and if it is supplied with null data, it will fail. If you process the exception within this code, then there is a chance it won't be noticed.

It is a matter of programming taste. I prefer to control submitted data prior to hitting this function.

Appreciate your feedback.

Regards.




Re: Function to split a string [message #394104 is a reply to message #394086] Wed, 25 March 2009 13:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Did that really take you a couple of days?

Hmmmm.....

And lose the WHEN OTHERS THEN NULL will you?
Hiding errors isn't big and it isn't clever.

Here's an alternative 2 ways of doing it. One will just return you a table of string fragments, with slightly less obscure code, and the other will turn that table into a pipelined data source that you can access as a set of rows from SQL. :
create type ty_str_split as table of varchar2(100)
/

create or replace function str_split_as_table (p_str   in varchar2
                                              ,p_delim in varchar2 default ',') 
return ty_str_split pipelined as

  v_tab    ty_str_split;
begin
  v_tab := str_split(p_str,p_delim);
  
  for i in 1..v_tab.last loop
    pipe row(v_tab(i));
  end loop;
  return;
end;;
/

create or replace function str_split  (p_str   in varchar2
                                     ,p_delim in varchar2 default ',') 
return ty_str_split as
  v_str      varchar2(32767);
  v_fields   pls_integer;
  v_substr   varchar2(32767);
  v_return   ty_str_split := ty_str_split();
begin
  v_str := p_delim||trim(p_delim from p_str)||p_delim;
  v_fields := length(v_str) - length(replace(v_str,p_delim,'')) - 1;
  v_return.extend(v_fields);
  
  for i in 1..v_fields loop
    v_substr := substr(v_str
                      ,instr(v_str,p_delim,1,i)+1  
                      ,instr(v_str,p_delim,1,i+1) - instr(v_str,p_delim,1,i) -1);
    v_return(i) := v_substr;
  end loop;
  return v_return;
end;;
/        

select *
from   table(str_split_as_table('asd,456,trfgh,4567'));

select str_split('asd,456,trfgh,4567') from dual;



Ok, I'm showing off, but debugging Db duplication scripts is slow and boring.
Re: Function to split a string [message #394105 is a reply to message #394103] Wed, 25 March 2009 13:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It will also ail if any of the string elements are bigger than 300 chrs.

Worse, when it fails, you will simply get a 'FUNCTION RETURNED WITHOUT VALUE' exception.

So - worst of both worlds - it will pass an exception up to the calling process, but it will also hide all details of what the exception that calused the failure was.

That When Others then null is strictly worse than having either a full blown exception handler dealing with all likely errors, or no exception handling at all.
Re: Function to split a string [message #394106 is a reply to message #394104] Wed, 25 March 2009 13:13 Go to previous messageGo to next message
morafaqm
Messages: 12
Registered: March 2009
Location: Denver
Junior Member
2 days - probably. I was busy.

I don't see much use of such function in SQL.


Probably more exotic than the use in some massive records processing.

Thanks for alternatives. Looks nice.
Re: Function to split a string [message #394107 is a reply to message #394105] Wed, 25 March 2009 13:24 Go to previous messageGo to next message
morafaqm
Messages: 12
Registered: March 2009
Location: Denver
Junior Member
This examle is just a template.
If 300 doesn't fit you, can be change to 32767 with the minimal effort.

During the last 6 years since this function was written, I never saw a need for a longer string elements.

Probably you are right in regards to exceptions processing.
But I would prefer to catch it at the earlier stages.

Thanks

Re: Function to split a string [message #394109 is a reply to message #394107] Wed, 25 March 2009 13:45 Go to previous messageGo to next message
Michel Cadot
Messages: 59280
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But I would prefer to catch it at the earlier stages.

You don't catch it, you hide it.
How could you say that you want to ignore ALL errors?
Isn't the caller that should be able to say if it wants to ignore or not the error?
The purpose of exceptions is exactly that: letting the caller decides what it wants to do with an error.
Imagine Oracle follows the same idea than you... you will never have any error in any case... easy to debug now when you have bad result (if you realize you have bad result), doesn't it?

Regards
Michel
Re: Function to split a string [message #394110 is a reply to message #394105] Wed, 25 March 2009 13:46 Go to previous messageGo to next message
morafaqm
Messages: 12
Registered: March 2009
Location: Denver
Junior Member
Your code works fine with some exceptions.
If leading elements are null, then they are not shown.
Same true for the tail elements and it also is not shown in my function.

But great example. Thanks again.


Re: Function to split a string [message #394111 is a reply to message #394109] Wed, 25 March 2009 13:56 Go to previous messageGo to next message
morafaqm
Messages: 12
Registered: March 2009
Location: Denver
Junior Member
These all are great points.

It still produces a full blown exception when delimiter is null.
Which is wrong I agree.

When the string is null, it will return a single element with null value. No exception.

String element length - needs to be changed to prevent Function returned without value result.

If a string element is null, it will be returned as null with no exceptions. If it is a trailing element, it won't be shown, which likely still has to be returned and I will make the change.

I will change element length or put some comment explaining what has to be done if long string elements are expected.

Thanks again. Great points.

Re: Function to split a string [message #394112 is a reply to message #394110] Wed, 25 March 2009 13:56 Go to previous messageGo to next message
Michel Cadot
Messages: 59280
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You seem to think that all errors that can come are those you thought of.
But what if you get an error because you are out of PGA memory? Then, if you add the return clause as JRowbottom suggested instead of getting a PL/SQL execution error saying your function does not return a value, your function returns a wrong partial result.
It is just an example, you can't imagine all the possible errors.

Regards
Michel
Re: Function to split a string [message #394114 is a reply to message #394112] Wed, 25 March 2009 14:08 Go to previous messageGo to next message
morafaqm
Messages: 12
Registered: March 2009
Location: Denver
Junior Member
I will think on whatever I see reasonable and I have my limitatons. I'm not trying to launch mini-copper into outer space here with the team of 7. Of course it won't work. Especially assuming that string elements can be as long as 32K and I have a billion of them to process. As a staring point it is good enough.

Regards
Re: Function to split a string [message #394235 is a reply to message #394114] Thu, 26 March 2009 04:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Surely if you are going to post code for others to use then 'good enough as a starting point' isn't quite the standard you should be aiming for.

You aren't addressing the point that your When Others Then Null actively hides any problems that occur. It doesn't handle them - instead it behaves as if you'd passed in no data. (Or at least it will once you've added a RETURN clause into the exception handler. Until then, it will just give you a 'Function Returned Without Error' exception.

How will you tell the difference from the calling procedure between a call that raises an error, and a call where you passed in NULL data?

Do you honestly believe that these situations should both be treated the same in all cases?



Re: Function to split a string [message #394975 is a reply to message #394235] Mon, 30 March 2009 23:26 Go to previous messageGo to next message
morafaqm
Messages: 12
Registered: March 2009
Location: Denver
Junior Member
How will you tell the difference from the calling procedure between a call that raises an error, and a call where you passed in NULL data?

If you pass NULL data to the function, then there is really nothing to do with it. I don't see it as an exceptional situation.
You can't expect any further processing when incoming data is null and I find it as a normal scenario.
For example you are reading "X" delimited strings from file and 1 line has nothing except LF/CF in it. It is not an exception, unless you want to consider it exceptional. Nothing to report.

Considering that this function usage makes sense within a certain application or automated processing which means that any interactive messaging is not available or very limited, then the user should define where to dump exceptions, stop processing or continue in case of error etc. It is what I mean by starting point.






Re: Function to split a string [message #395009 is a reply to message #394975] Tue, 31 March 2009 00:52 Go to previous messageGo to next message
Frank
Messages: 7877
Registered: March 2000
Senior Member
You just made exactly clear why one should not use WHEN OTHERS:
you have taken into account the possibility that a NULL value is given as parameter, and you decided you don't want the procedure to error out on that. Fine, that decision is upto you. But that means you should write an exception handler SPECIFIC to that situation (catching only e.g. a NO_DATA_FOUND exception)

You also say you want the user of this procedure to decide what to do with other exceptions. Too bad you already handled them. No way for me as a consumer of your function to decide what to do (or even to know something went wrong).

Handling exceptions in some helper function by means of a WHEN OTHERS is 99.99% of the time plain wrong.
Re: Function to split a string [message #442623 is a reply to message #33545] Tue, 09 February 2010 09:02 Go to previous messageGo to next message
morrisp6
Messages: 5
Registered: January 2009
Location: Manchester, England
Junior Member
Hi All,

I have tried to follow this thread and the code in it, and i am very lost.. i am pretty new to Oracle, and not really used Functions at all really.

I have copied a couple of the functions in this thread and had a play with them, but i am completely out of my depth here, and don't know what I am altering...

I will explain my problem i have been given, and if you could help me modify, or explain how i do it that would be great!:

I have a load of user comments in a table, along with some key information that allows me to link to other information. I need to splilt the free text comments into each of the words, and the sequence they are in. For instance:

ROW_ID, COMMENTS
--------------------
1234, This is an example

I need to turn that into:

ROW_ID,SEQ_NO,TEXT
-------------------
1234,1,This
1234,2,is
1234,3,an
1234,4,example


I am kind of lost as to how i do this... can anyone help?

Many thanks
Paul




Re: Function to split a string [message #442627 is a reply to message #442623] Tue, 09 February 2010 09:43 Go to previous messageGo to next message
Michel Cadot
Messages: 59280
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what you tried and we will tell you what is wrong.

Regards
Michel

[Updated on: Tue, 09 February 2010 11:00]

Report message to a moderator

Re: Function to split a string [message #442629 is a reply to message #33545] Tue, 09 February 2010 09:54 Go to previous messageGo to next message
morrisp6
Messages: 5
Registered: January 2009
Location: Manchester, England
Junior Member
Thanks for the quick reply.

What i tried was this:
I created the function that JRowbottom detailed in this thread, as follows:

-------------
create or replace function str_split  (p_str   in varchar2
                                     ,p_delim in varchar2 default ',') 
return ty_str_split as
  v_str      varchar2(32767);
  v_fields   pls_integer;
  v_substr   varchar2(32767);
  v_return   ty_str_split := ty_str_split();
begin
  v_str := p_delim||trim(p_delim from p_str)||p_delim;
  v_fields := length(v_str) - length(replace(v_str,p_delim,'')) - 1;
  v_return.extend(v_fields);
  
  for i in 1..v_fields loop
    v_substr := substr(v_str
                      ,instr(v_str,p_delim,1,i)+1  
                      ,instr(v_str,p_delim,1,i+1) - instr(v_str,p_delim,1,i) -1);
    v_return(i) := v_substr;
  end loop;
  return v_return;
end;;
/        

--------------

I then tried to execute the following SQL, to create a table of the output.
create table c_test as
select 
row_id
str_split(comments_long,' ') as testy
from
MY_TABLE

I get the following error though.
ORA-22913: must specify table name for nested table column or attribute

And this is where i am lost ! Sad

Many thanks for any pointers / advice!

Paul

[Mod Edit: Added code tags]

[Updated on: Tue, 09 February 2010 10:07] by Moderator

Report message to a moderator

Re: Function to split a string [message #442631 is a reply to message #442627] Tue, 09 February 2010 10:11 Go to previous messageGo to next message
morafaqm
Messages: 12
Registered: March 2009
Location: Denver
Junior Member
Could you be more specific?
Re: Function to split a string [message #442634 is a reply to message #442631] Tue, 09 February 2010 10:36 Go to previous messageGo to next message
pablolee
Messages: 2634
Registered: May 2007
Location: Scotland
Senior Member
morafaqm wrote on Tue, 09 February 2010 16:11
Could you be more specific?

What about? Michel was quite specific, he asked morris to post what he tried, and Michel would help him with what was wrong. What's not specific about that?

[edit: Typo]

[Updated on: Tue, 09 February 2010 10:37]

Report message to a moderator

Re: Function to split a string [message #442638 is a reply to message #442634] Tue, 09 February 2010 11:49 Go to previous messageGo to next message
morafaqm
Messages: 12
Registered: March 2009
Location: Denver
Junior Member
It was pretty idiotic to ask "be more specific" without reading the thread. Almost as much as your clarification.
Re: Function to split a string [message #442640 is a reply to message #442629] Tue, 09 February 2010 12:01 Go to previous messageGo to next message
Michel Cadot
Messages: 59280
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@morrisp6

1/ Your code does not compile, please post a code that compile
2/ Your query is invalid, post a valid query
3/ Your table column names do not match those in your first post
4/ once all is fixed I got:
SQL> select 
  2  row_id,
  3  str_split(comments_long,' ') as testy
  4  from
  5  t;
    ROW_ID
----------
TESTY
---------------------------------------------
      1234
TY_STR_SPLIT('This', 'is', 'an', 'example')

So please your version number with 4 decimals and a correct post.
Also use SQL*Plus and copy and paste your session.

Regards
Michel

[Updated on: Tue, 09 February 2010 12:45]

Report message to a moderator

Re: Function to split a string [message #442643 is a reply to message #442623] Tue, 09 February 2010 12:43 Go to previous messageGo to next message
Michel Cadot
Messages: 59280
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are ways to do it in SQL only, here's one:
create table t (row_id integer, comments varchar2(100));
insert into t values(1234, 'This is an example');
insert into t values(2345, 'And here is another example');
commit;

SQL> select * from t;
    ROW_ID COMMENTS
---------- ----------------------------------
      1234 This is an example
      2345 And here is another example

2 rows selected.

SQL> col comm format a20
SQL> with
  2    lines as (select level line from dual connect by level <= 10),
  3    data as (select row_id, ' '||trim(comments)||' ' comments from t)
  4  select row_id, line,
  5         substr(comments,
  6                instr(comments,' ',1,line)+1,
  7                instr(comments,' ',1,line+1)-instr(comments,' ',1,line)-1
  8               ) comm
  9  from data, lines
 10  where line < length(comments)-length(replace(comments,' ',''))
 11  order by 1, 2
 12  /
    ROW_ID       LINE COMM
---------- ---------- --------------------
      1234          1 This
      1234          2 is
      1234          3 an
      1234          4 example
      2345          1 And
      2345          2 here
      2345          3 is
      2345          4 another
      2345          5 example

9 rows selected.

Regards
Michel

[Updated on: Tue, 09 February 2010 12:44]

Report message to a moderator

Re: Function to split a string [message #442644 is a reply to message #33545] Tue, 09 February 2010 12:48 Go to previous messageGo to next message
morrisp6
Messages: 5
Registered: January 2009
Location: Manchester, England
Junior Member
Michel,

you are a genius... plain and simple.

thankyou ever so much for this, i will now learn exactly how you performed this data witchcraft! Razz

Regards
Paul
Re: Function to split a string [message #452561 is a reply to message #33545] Thu, 22 April 2010 10:34 Go to previous messageGo to previous message
ali16
Messages: 3
Registered: April 2010
Junior Member
Hi,

I'm really confused. i believe this is probably the best place for this post. apologies if it is not.

at the moment my code allows the user to send a request for one bit of information at a time by sending its ID number. for example for the user to get all the first names they request ID 1 and for surname ID 2 etc.

how ever i would like the user to be able to request more than one bit of information like first name and surname, however this would mean the information has to be sent as a var char not a number which provides me with an error as i complete a where clause connected to a number field.

so my idea was to run through each of the requested information to populate a table. but i do not know how to separate a comma delimited file.

Any help?

sorry if you maybe repeating previous answers, I'm new to this, and self taught.

thanks

Ali

Previous Topic: HELP!!!
Next Topic: using more than four db links in a procedure
Goto Forum:
  


Current Time: Wed Oct 01 01:29:02 CDT 2014

Total time taken to generate the page: 0.11428 seconds