Re: How to extract indiviual substring

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Thu, 26 Jul 2001 10:09:26 +0200
Message-ID: <9joj7n$2fc$1@s1.read.news.oleane.net>

You can have a look at dbms_utility.comma_to_table function or at the following function from Thomas Kyte:

ops$tkyte_at_8i> create or replace type myTableType as table of number;   2 /

Type created.

ops$tkyte_at_8i> create or replace function in_list( p_string in varchar2 ) return myTableType   2 as

  3      l_data             myTableType := myTableType();
  4      l_string           long default p_string || ',';
  5      l_n                number;

  6 begin
  7

  8          loop
  9                  exit when l_string is null;
 10                  l_data.extend;
 11                  l_n := instr( l_string, ',' );
 12                  l_data( l_data.count ) := substr( l_string, 1, l_n-
1 );
 13                  l_string := substr( l_string, l_n+1 );
 14          end loop;
 15          return l_data;

 16 end;
 17 /

Function created.

These two functions convert a comma separated list to a table.

--
Hope this helps
Michel


"C Chang" <cschang_at_maxinter.net> a écrit dans le message news: 3B5F856F.7CDE_at_maxinter.net...


> As a newbie, I like to learn how to extract substring from a long string

> wiht some kind of sommon separator, ex "," or ";".  for example I have a

> long string like  - Name;123456;45.67 as an input variable, How do I

> extract the substring into an array as { Name, 1233456, 45.67} ? I have

> tried combine substr and lenght functions but no success.  Can Any guru

> help me?  Thanks.

>

> C Chang

Received on Thu Jul 26 2001 - 03:09:26 CDT