How to use the Perl Split function pl/SQL?

Asked By Warren Donaldson 0 points N/A Posted on - 11/19/2012

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

• Status: Open
• Question Views: 1302
• Answer Count: 2
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: Server Software

Best Answer by Aust Wilson

Go To Solution

Answered By Deborah Antoaneta 0 points N/A #151045

How to use the Perl Split function pl/SQL?

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, ' , ');

 

About Deborah Antoaneta

Questions
3

Answers
81

Best Answers
13

• Vote Up 0 Vote Down

• Posted on - 11/19/2012
• Question Category: Server Software

Best Answer

Best Answer

Answered By Aust Wilson 5 points N/A #151044

How to use the Perl Split function pl/SQL?

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)

About Aust Wilson

Questions
1

Answers
62

Best Answers
12

• Vote Up 0 Vote Down

• Posted on - 11/30/2012
• Question Category: Server Software