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  |
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 #33550 is a reply to message #33545] |
Mon, 18 October 2004 18:46   |
 |
Barbara Boehmer
Messages: 9105 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 #129975 is a reply to message #129968] |
Wed, 27 July 2005 09:05   |
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 #130372 is a reply to message #130194] |
Fri, 29 July 2005 10:40   |
William Robertson
Messages: 1643 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 #394085 is a reply to message #394081] |
Wed, 25 March 2009 12:06   |
 |
Michel Cadot
Messages: 68758 Registered: March 2007 Location: Saint-Maur, France, https...
|
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 #394104 is a reply to message #394086] |
Wed, 25 March 2009 13:05   |
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 #442623 is a reply to message #33545] |
Tue, 09 February 2010 09:02   |
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 #442629 is a reply to message #33545] |
Tue, 09 February 2010 09:54   |
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 ! 
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 #442634 is a reply to message #442631] |
Tue, 09 February 2010 10:36   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
morafaqm wrote on Tue, 09 February 2010 16:11Could 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 #442640 is a reply to message #442629] |
Tue, 09 February 2010 12:01   |
 |
Michel Cadot
Messages: 68758 Registered: March 2007 Location: Saint-Maur, France, https...
|
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   |
 |
Michel Cadot
Messages: 68758 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|
|
|
Goto Forum:
Current Time: Sat Jun 14 17:17:25 CDT 2025
|