Hello,
How can I use the split() function in Perl if I want to split strings using the PL/SQL functions? What are the procedures and codings?
Â
Thanks
Hi!
Use this query to split strings in PL SQL;
function str2tbl    (p_str in varchar2,p_delim in varchar2 default ' . ') return m is
1_str        long default p_str | | p_delim;
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 str2tbl;
You can use this example for use;
TYPEÂ myTabletype is table of varchar2(100);
v_array mytabletype;
v_arry := str2tbl (string, ' , ');
Â
The Split() Function in Perl is used to break a string into an array on a specific pattern. The PATTERN is but a regular expression, which could be a single character. Upon every instance of the PATTERN, the STRING is split, that is by default. But you can put a LIMIT to that in some instances, like this:
split /PATTERN/,EXPR,LIMIT
split /PATTERN/,EXPR
split /PATTERN/
split
If you are using PostgreSQL, there is no built-in Split Function in it. But you could write it yourself in the form of PL/Perl. You can also check out this:
DECLARE
   i int := 0;
   word text;
   result text := ''{'';
   result_arr text[];
 BEGIN
   LOOP
     i := i + 1;
     SELECT INTO word split_part($1, $2, i);
     IF word = '''' THEN
       EXIT;
     END IF;
     IF i > 1 THEN
       result := result || '',"'' || word || ''"'';
     ELSE
       result := result || ''"'' || word || ''"'';
     END IF;
   END LOOP;
   result := result || ''}'';
   result_arr := result;
   RETURN result_arr;
 END
' LANGUAGE 'plpgsql';
test=# select split('a,b,c',',');
 split
———
 {a,b,c}
(1 row)
test=# select a[1] from (select split('a,b,c',',') as a) as t;
 a
—
 a
(1 row)
