Simple solution to split a string into multiple rows in Oracle is a combination of a query over the “table” DUAL and using clause CONNECT BY. All have demonstrated a simple example.

[Česká verze článku – Rozdělení řetězce do více řádků pomocí dotazu nad tabulkou DUAL]

Idea that there is the following string:

Anna,Martin,Sebastian,Gabriela,Pavel,

We need to split this string so that the result was a list of names, each name on one line, like this:

Anna
Martin
Sebastian
Gabriela
Pavel

How to achieve this (in simple terms) shows the following SQL query:

SELECT substr( 'Anna,Martin,Sebastian,Gabriela,Pavel,',
         ( case when rownum = 1
           then 1
           else instr( 'Anna,Martin,Sebastian,Gabriela,Pavel,', ',', 1, rownum - 1 ) + 1 end ),

         instr( substr( 'Anna,Martin,Sebastian,Gabriela,Pavel,',
         		( case when rownum = 1
                  then 1
                  else instr( 'Anna,Martin,Sebastian,Gabriela,Pavel,', ',', 1, rownum - 1 ) + 1 end )
       		), ',' ) - 1

       ) as products
FROM dual
CONNECT BY LEVEL <= length( 'Anna,Martin,Sebastian,Gabriela,Pavel,' )
                  - length ( replace('Anna,Martin,Sebastian,Gabriela,Pavel,', ',') ) ;

Post to Twitter

Leave a comment

Your email address will not be published. Required fields are marked *