PL/SQL: Split URL Parameters

I've always had this phobia of anything to do with strings. Might be because I find it hard to think that way...and by "that way" I have no idea what I'm talking about.

Whenever I come across this problem, my first instinct is to go through every character and build a string, something like this:

DECLARE
  l_string VARCHAR2(100) := 'THIS_LITTLE_STRING';
  l_character VARCHAR2(1);
BEGIN
  FOR i IN 1..LENGTH( l_string ) LOOP
    l_character := l_character || SUBSTR( l_string, i, 1 );
  END LOOP;
END;
/

Of course that means I have to go through the loop at least 18 times. How could I loop less?

Well, I would need to know the delimiter first, in this case, it will be the underscore character:

DECLARE
  l_string VARCHAR2(100) := 'THIS_LITTLE_STRING';
  l_character VARCHAR2(1);
  l_delimiter VARCHAR2(1) := '_';
BEGIN
...

OK, that helps. So what can I do with that?

DECLARE
  l_string VARCHAR2(100) := 'THIS_LITTLE_STRING';
  l_new_string VARCHAR2(100) := l_string;
  l_token VARCHAR2(30);
  l_character VARCHAR2(1);
  l_delimiter VARCHAR2(1) := '_';
BEGIN
  LOOP
    EXIT WHEN l_new_string IS NULL;
    l_token := SUBSTR( l_new_string, 1, INSTR( l_new_string, '_', 1 ) );
...

OK, now I remember what I always get confused. There seems to be this endless stream of SUBSTR and INSTR involved. It's difficult to follow...for me anyway. So here's what I've come up with for now. I am aware of regular expressions and the DBMS_UTILITY.COMMA_TO_TABLE procedures, but for some reason, I like to reinvent the wheel. No, not really, I just find it challenging. More challenging would be for me to start using regular expressions...Anyway, here goes my attempt at splitting a URL string:

DECLARE
  TYPE my_table IS TABLE OF VARCHAR2(100);
  l_table MY_TABLE := MY_TABLE();
  l_string VARCHAR2(1000) := 'TESTING=YES&&BOLLOCKS=SOMETHING&&&testing=DF';
  l_keyvalue VARCHAR2(100);
  l_delimiter VARCHAR2(5) := '&&';
  l_delimiter_length INTEGER := LENGTH( l_delimiter );
  l_counter INTEGER := 0;
  l_new VARCHAR2(4000);
  l_start INTEGER := 1;
  l_end INTEGER := 0;
BEGIN
  IF SUBSTR( l_string, -1, 1 ) <> l_delimiter THEN
    l_string := l_string || l_delimiter;
  END IF;

  l_new := l_string;

  LOOP
    l_counter := l_counter + 1;
    l_end := INSTR( l_new, l_delimiter, 1 );
    l_keyvalue := SUBSTR( l_new, 1, l_end - 1 );
    EXIT WHEN l_keyvalue IS NULL;
    l_table.EXTEND(1);
    l_table(l_counter) := l_keyvalue;
    l_start := l_start + ( l_end + ( l_delimiter_length - 1 ) );
    l_new := SUBSTR( l_string, l_start );
  END LOOP;
  p( 'table counter:  ' || l_table.COUNT );
  FOR I IN 1..l_table.COUNT LOOP
    p( 'string:  ' || l_table(i) );
  END LOOP;
END;
/

CJUSTICE@TESTING>@T
table counter:  3
string:  TESTING=YES
string:  BOLLOCKS=SOMETHING
string:  &testing=DF

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

Voila! My own URL String Tokenizer.

Now I just have to write something that will split up the key/value pairs...