Writing a pure SQL based String Tokenizer

9 thoughts on “Writing a pure SQL based String Tokenizer”

1. SELECT SUBSTR(X,Y + 1, Z – 1 – Y)
   FROM (
   SELECT X, INSTR(X,DELIMITER,1,ROWNUM) Y, INSTR(X,DELIMITER,1,ROWNUM+1) Z
   FROM (SELECT ‘,’ || ‘A,BB,CC’ || ‘,’ X, ‘,’ DELIMITER FROM DUAL), ALL_TABLES
   WHERE INSTR(X,DELIMITER,1,ROWNUM+1) != 0
   )

2. hi guyz i tried to develop a number puzzle in forms6i got strucked in middle .. can any one help me out ..
   declare
   temp varchar2(40);
   lbl varchar2(40);
   begin
   lbl:=get_item_property(name_in(‘system.trigger_item’),label);
   temp:= :system.trigger_item;
   if get_item_property(temp,VISIBLE)= ‘TRUE’ then
   set_item_property(:parameter.HIDENUM,VISIBLE,PROPERTY_TRUE);
   set_item_property(:parameter.HIDENUM,ENABLED,PROPERTY_TRUE);
   set_item_property(:parameter.HIDENUM,label,lbl);
   go_item(:parameter.HIDENUM);
   set_item_property(temp,VISIBLE,PROPERTY_FALSE);
   :parameter.HIDENUM:=temp;
   end if;
   /*if get_item_property(‘number_game.TWO’,displayed)=’TRUE’ then
   set_item_property(‘number_game.TWO’,displayed,’FALSE’);
   end if;
   if get_item_property(‘number_game.THREE’,displayed)=’TRUE’ then
   set_item_property(‘number_game.THREE’,displayed,’FALSE’);
   end if;*/
   if lbl =’EXIT’ then exit_form(no_validate);
   end if;
   end;

3. Hi,
   I found and already used a nice solution from O’Reillys "SQL Cookbook".
   Those days I needed to convert delimited data into a multi-valued IN-List.
   I just changed the query a bit to work as a string tokenizer. See:

   SELECT RTRIM(
   SUBSTR( x.token
   , INSTR(x.token, ‘&Delimiter’, 1, iter.pos) + 1
   ,INSTR(x.token, ‘&Delimiter’, 1, iter.pos + 1) – INSTR(x.token, ‘&Delimiter’, 1, iter.pos)
   )
   , ‘&Delimiter’
   ) token
   , iter.pos AS Position
   FROM (SELECT ‘&Delimiter’ || ‘&StringToTokenize’ || ‘&Delimiter’ token
   FROM dual
   ) x
   , (SELECT rownum AS pos
   FROM all_objects
   ) iter
   WHERE iter.pos

4. (Sorry,the PRE tags got stripped)

5. My own variant using the MODEL clause

   SELECT var_list, var_list2
   FROM ( select ‘red,blue,yellow’ var_list,’,’ delim from dual )
   MODEL
   PARTITION BY (var_list, delim)
   DIMENSION BY (0 AS z)
   MEASURES (var_list var_list2,
   0 vcnt, 0 as vstart, 0 as vend)
   RULES ITERATE (4000) UNTIL (ITERATION_NUMBER+1 = vcnt[0] )
   (vcnt[ITERATION_NUMBER]=
   nvl(length(cv(var_list)) – length(replace(cv(var_list),cv(delim))) + 1,1),
   vstart[ITERATION_NUMBER] =
   case when ITERATION_NUMBER = 0 then 1
   else instr(cv(var_list),cv(delim),1,ITERATION_NUMBER)+1 end,
   vend[ITERATION_NUMBER] =
   case when nvl(instr(cv(var_list),cv(delim),1,ITERATION_NUMBER+1),0) = 0
   then nvl(length(cv(var_list))+1,1)
   else instr(cv(var_list),cv(delim),1,ITERATION_NUMBER+1) end,
   var_list2[ITERATION_NUMBER] =
   substr(cv(var_list),vstart[ITERATION_NUMBER],
   vend[ITERATION_NUMBER] – vstart[ITERATION_NUMBER])
   )
   order by var_list, vstart;

6. Gentlemen, you have started your engines! Two beatiful solutions, that surpass my original proposal. Thanks for sharing your ideas!

   Lucas

7. My thoughts about this (only because you asked for it 🙂 ):

   with input as 
   ( select 'red,blue,yellow' string_to_tokenize 
     , ',' delimiter 
     from dual 
   )
   , temp as 
   ( select delimiter || string_to_tokenize || delimiter stt 
   , delimiter del 
     from input 
   ) 
   select substr( stt, instr( stt, del, 1, d ) + 1
   ,      instr( stt, del, 1, d + 1 ) - instr( stt, del, 1, d ) - 1 ) 
   from   temp 
   model 
     partition by ( stt, del ) 
     dimension by ( 0 x ) 
     measures ( 1 d, ( select length( translate( stt, del || stt, del ) ) - 2 
                       from   temp 
                     ) noe 
               ) 
     rules iterate( 1000 ) 
     until iteration_number >= noe[0] ( d[iteration_number] = iteration_number + 1 ) 
   

   For the thoughts of some other people on something simulare, see Adrian’s Oracle Page at http://mysite.wanadoo-members.co.uk/adriansoraclepages/demos/10g/scripts/string_to_table_sql_only.sql
   Anton

8. BTW, is there a way to format code in a comment? Because all the line brakes are gone. 🙁

9. Hi Lucas,

   you are keeping me away from work! But a SQL puzzle in the morning is good for my brain! 🙂
   Here is my alternate solution using CONNECT BY 

   SELECT LEVEL 
   ,      SUBSTR ( STRING_TO_TOKENIZE 
                 , DECODE( LEVEL, 1, 1, INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL-1)+1)
                 , INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL) 
                   - DECODE( LEVEL, 1, 1, INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL-1)+1)
                 ) 
   FROM ( SELECT '&String_To_Tokenize'||'&Delimiter' AS STRING_TO_TOKENIZE 
          ,      '&Delimiter' AS DELIMITER 
          FROM DUAL 
        ) 
   CONNECT 
   BY     INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL) > 0 
   ORDER 
   BY     LEVEL ASC ; 
   

   Patrick
   Check out my APEX blog: http://inside-apex.blogspot.com/

Comments are closed.