Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

jdavidb (1361)

jdavidb
  (email not shown publicly)
http://voiceofjohn.blogspot.com/

J. David Blackstone has a Bachelor of Science in Computer Science and Engineering and nine years of experience at a wireless telecommunications company, where he learned Perl and never looked back. J. David has an advantage in that he works really hard, he has a passion for writing good software, and he knows many of the world's best Perl programmers.

Journal of jdavidb (1361)

Friday July 21, 2006
08:01 AM

Oracle gem of the day: split in PL/SQL

[ #30363 ]

This is actually prettier and more elegant than I usually get for PL/SQL. :) The routine relies on a function

betwnstr

, which you may find in Feuerstein and Pribyl's Oracle PL/SQL Programming from O'Reilly, 3rd edition, page 216, or implement yourself. Incidentally, one bug I had along the way was using SUBSTR here without thinking about what I really wanted to do.

Generalizing to take any arbitrary character to split on as a parameter is left as an exercise to the reader. As is generalizing to take any arbitrary string, followed by generalizing to take any arbitrary regular expression.

FUNCTION comma_split(p_text VARCHAR2)
RETURN OWA_TEXT.VC_ARR
IS
  v_array OWA_TEXT.VC_ARR;
  v_lastpos INTEGER := 0;
  v_pos INTEGER;
  v_count INTEGER := 0;
BEGIN
  WHILE v_lastpos <= LENGTH(p_text)
  LOOP
    v_count := v_count + 1;
    v_pos := INSTR(p_text, ',', 1, v_count);
    IF v_pos = 0
    THEN
      v_pos := LENGTH(p_text) + 1;
    END IF;
    v_array(v_count) := betwnstr(p_text, v_lastpos + 1, v_pos - 1);
    v_lastpos := v_pos;
  END LOOP;
  RETURN v_array;
END comma_split;

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.