Splitting string into multiple rows in Oracle

Question

I know this has been answered to some degree with PHP and MYSQL  but I was wondering if someone could teach me the simplest approach to splitting a string  comma delimited  into multiple rows in Oracle 10g  preferably  and 11g    The table is as follows   Name   Project   Error  108    test      Err1  Err2  Err3 109    test2     Err1   I want to create the following   Name   Project   Error 108    Test      Err1 108    Test      Err2  108    Test      Err3  109    Test2     Err1   I ve seen a few potential solutions around stack  however they only accounted for a single column  being the comma delimited string   Any help would be greatly appreciated

User · Answer

I d like to add another method  This one uses recursive querys  something I haven t seen in the other answers  It is supported by Oracle since 11gR2   with cte0 as       select phone number x     from hr employees    cte1 xstr xrest xremoved  as           select x  x  null         from cte0     union all                 select xstr              case when instr xrest        0 then null else substr xrest instr xrest      1  end              case when instr xrest        0 then xrest else substr xrest 1 instr xrest      - 1  end         from cte1         where xrest is not null   select xstr  xremoved from cte1   where xremoved is not null order by xstr   It is quite flexible with the splitting character  Simply change it in the INSTR calls

User · Answer

There is a huge difference between the below two    splitting a single delimited string splitting delimited strings for multiple rows in a table     If you do not restrict the rows  then the CONNECT BY clause would produce multiple rows and will not give the desired output     For single delimited string  look at Split single comma delimited string into rows For splitting delimited strings in a table  look at Split comma delimited strings in a table   Apart from Regular Expressions  a few other alternatives are using    XMLTable MODEL clause   Setup  SQL gt  CREATE TABLE t     2    ID          NUMBER GENERATED ALWAYS AS IDENTITY    3    text        VARCHAR2 100    4      Table created   SQL gt  SQL gt  INSERT INTO t  text  VALUES   word1  word2  word3     1 row created   SQL gt  INSERT INTO t  text  VALUES   word4  word5  word6     1 row created   SQL gt  INSERT INTO t  text  VALUES   word7  word8  word9     1 row created   SQL gt  COMMIT   Commit complete   SQL gt  SQL gt  SELECT   FROM t           ID TEXT ---------- ----------------------------------------------          1 word1  word2  word3          2 word4  word5  word6          3 word7  word8  word9  SQL gt    Using XMLTABLE   SQL gt  SELECT id    2         trim COLUMN VALUE  text   3  FROM t    4    xmltable        5       REPLACE text                6               7             ID TEXT ---------- ------------------------          1 word1          1 word2          1 word3          2 word4          2 word5          2 word6          3 word7          3 word8          3 word9  9 rows selected   SQL gt    Using MODEL clause   SQL gt  WITH   2  model param AS   3         4            SELECT id    5                      text AS orig str     6                         7                             text   8                                                                 AS mod str     9                   1                                             AS start pos    10                   Length text                                    AS end pos    11                    Length text  - Length Replace text           1 AS element count    12                   0                                             AS element no    13                   ROWNUM                                        AS rn  14            FROM   t    15     SELECT   id   16              trim Substr mod str  start pos  end pos-start pos   text  17     FROM        18                     SELECT    19                     FROM   model param MODEL PARTITION BY  id  rn  orig str  mod str   20                     DIMENSION BY  element no   21                     MEASURES  start pos  end pos  element count   22                     RULES ITERATE  2000   23                     UNTIL  ITERATION NUMBER 1   element count 0    24                       start pos ITERATION NUMBER 1    instr cv mod str        1  cv element no     1   25                     end pos iteration number 1    instr cv mod str        1  cv element no    1     26                    27     WHERE    element no    0  28     ORDER BY mod str    29           element no  30             ID TEXT ---------- --------------------------------------------------          1 word1          1 word2          1 word3          2 word4          2 word5          2 word6          3 word7          3 word8          3 word9  9 rows selected   SQL gt

User · Answer

I would like to propose a different approach using a PIPELINED table function  It s somewhat similar to the technique of the XMLTABLE  except that you are providing your own custom function to split the character string   -- Create a collection type to hold the results CREATE OR REPLACE TYPE typ str2tbl nst AS TABLE OF VARCHAR2 30      -- Split the string according to the specified delimiter CREATE OR REPLACE FUNCTION str2tbl     p string    VARCHAR2    p delimiter CHAR DEFAULT        RETURN typ str2tbl nst PIPELINED AS   l tmp VARCHAR2 32000     p string    p delimiter    l pos NUMBER  BEGIN   LOOP     l pos    INSTR  l tmp  p delimiter        EXIT WHEN NVL  l pos  0     0      PIPE ROW   RTRIM  LTRIM  SUBSTR  l tmp  1  l pos-1             l tmp    SUBSTR  l tmp  l pos 1      END LOOP  END str2tbl     -- The problem solution SELECT name          project          TRIM COLUMN VALUE  error   FROM t  TABLE str2tbl error      Results         NAME PROJECT    ERROR ---------- ---------- --------------------        108 test       Err1        108 test       Err2        108 test       Err3        109 test2      Err1   The problem with this type of approach is that often the optimizer won t know the cardinality of the table function and it will have to make a guess  This could be potentialy harmful to your execution plans  so this solution can be extended to provide execution statistics for the optimizer   You can see this optimizer estimate by running an EXPLAIN PLAN on the query above   Execution Plan ---------------------------------------------------------- Plan hash value  2402555806  ----------------------------------------------------------------------------------------------   Id    Operation                            Name      Rows    Bytes   Cost   CPU   Time       ----------------------------------------------------------------------------------------------     0   SELECT STATEMENT                               16336     366K     59    0   00 00 01       1    NESTED LOOPS                                  16336     366K     59    0   00 00 01       2     TABLE ACCESS FULL                  T             2      42       3    0   00 00 01       3     COLLECTION ITERATOR PICKLER FETCH  STR2TBL    8168   16336      28    0   00 00 01   ----------------------------------------------------------------------------------------------   Even though the collection has only 3 values  the optimizer estimated 8168 rows for it  default value   This may seem irrelevant at first  but it may be enough for the optimizer to decide for a sub-optimal plan   The solution is to use the optimizer extensions to provide statistics for the collection   -- Create the optimizer interface to the str2tbl function CREATE OR REPLACE TYPE typ str2tbl stats AS OBJECT     dummy NUMBER     STATIC FUNCTION ODCIGetInterfaces   p interfaces OUT SYS ODCIObjectList     RETURN NUMBER     STATIC FUNCTION ODCIStatsTableFunction   p function  IN  SYS ODCIFuncInfo                                             p stats     OUT SYS ODCITabFuncStats                                             p args      IN  SYS ODCIArgDescList                                             p string    IN  VARCHAR2                                             p delimiter IN  CHAR DEFAULT         RETURN NUMBER       -- Optimizer interface implementation CREATE OR REPLACE TYPE BODY typ str2tbl stats AS   STATIC FUNCTION ODCIGetInterfaces   p interfaces OUT SYS ODCIObjectList     RETURN NUMBER   AS   BEGIN     p interfaces    SYS ODCIObjectList   SYS ODCIObject   SYS    ODCISTATS2          RETURN ODCIConst SUCCESS    END ODCIGetInterfaces     -- This function is responsible for returning the cardinality estimate   STATIC FUNCTION ODCIStatsTableFunction   p function  IN  SYS ODCIFuncInfo                                             p stats     OUT SYS ODCITabFuncStats                                             p args      IN  SYS ODCIArgDescList                                             p string    IN  VARCHAR2                                             p delimiter IN  CHAR DEFAULT         RETURN NUMBER   AS   BEGIN     -- I m using basically half the string lenght as an estimator for its cardinality     p stats    SYS ODCITabFuncStats  CEIL  LENGTH  p string     2          RETURN ODCIConst SUCCESS    END ODCIStatsTableFunction   END     -- Associate our optimizer extension with the PIPELINED function    ASSOCIATE STATISTICS WITH FUNCTIONS str2tbl USING typ str2tbl stats    Testing the resulting execution plan   Execution Plan ---------------------------------------------------------- Plan hash value  2402555806  ----------------------------------------------------------------------------------------------   Id    Operation                            Name      Rows    Bytes   Cost   CPU   Time       ----------------------------------------------------------------------------------------------     0   SELECT STATEMENT                                   1      23      59    0   00 00 01       1    NESTED LOOPS                                      1      23      59    0   00 00 01       2     TABLE ACCESS FULL                  T             2      42       3    0   00 00 01       3     COLLECTION ITERATOR PICKLER FETCH  STR2TBL       1       2      28    0   00 00 01   ----------------------------------------------------------------------------------------------   As you can see the cardinality on the plan above is not the 8196 guessed value anymore  It s still not correct because we are passing a column instead of a string literal to the function    Some tweaking to the function code would be necessary to give a closer estimate in this particular case  but I think the overall concept is pretty much explained here   The str2tbl function used in this answer was originally developed by Tom Kyte  https   asktom oracle com pls asktom f p 100 11 0    P11 QUESTION ID 110612348061  The concept of associating statistics with object types can be further explored by reading this article  http   www oracle-developer net display php id 427  The technique described here works in 10g

User · Answer

This may be an improved way  also with regexp and connect by    with temp as       select 108 Name   test  Project   Err1  Err2  Err3  Error  from dual     union all     select 109   test2    Err1  from dual   select distinct   t name  t project    trim regexp substr t error           1  levels column value    as error from    temp t    table cast multiset select level from dual connect by  level  lt   length  regexp replace t error               1  as sys OdciNumberList   levels order by name   EDIT  Here is a simple  as in   not in depth   explanation of the query    length  regexp replace t error               1 uses regexp replace to erase anything that is not the delimiter  comma in this case  and length  1 to get how many elements  errors  are there   The select level from dual connect by level  lt         uses a hierarchical query to create a column with an increasing number of matches found  from 1 to the total number of errors   Preview   select level  length  regexp replace  Err1  Err2  Err3                1 as max  from dual connect by level  lt   length  regexp replace  Err1  Err2  Err3                1  table cast multiset        as sys OdciNumberList   does some casting of oracle types    The cast multiset         as sys OdciNumberList transforms multiple collections  one collection for each row in the original data set  into a single collection of numbers  OdciNumberList  The table   function transforms a collection into a resultset   FROM without a join creates a cross join between your dataset and the multiset  As a result  a row in the data set with 4 matches will repeat 4 times  with an increasing number in the column named  column value     Preview   select   from  temp t  table cast multiset select level from dual connect by  level  lt   length  regexp replace t error               1  as sys OdciNumberList   levels  trim regexp substr t error           1  levels column value   uses the column value as the nth appearance ocurrence parameter for regexp substr  You can add some other columns from your data set  t name  t project as an example  for easy visualization    Some references to Oracle docs    REGEXP REPLACE REGEXP SUBSTR Extensibility Constants  Types  and Mappings  OdciNumberList  CAST  multiset  Hierarchical Queries

User · Answer

i had used the DBMS UTILITY comma to  table function actually its working the code as follows   declare l tablen  BINARY INTEGER  l tab     DBMS UTILITY uncl array  cursor cur is select   from qwer  rec cur rowtype  begin open cur  loop fetch cur into rec  exit when cur notfound  DBMS UTILITY comma to table        list     gt  rec val       tablen   gt  l tablen       tab      gt  l tab   FOR i IN 1    l tablen LOOP     DBMS OUTPUT put line i             l tab i    END LOOP  end loop  close cur  end     i had used my own table and column names

User · Answer

regular expressions is a wonderful thing     with temp as           select 108 Name   test  Project   Err1  Err2  Err3  Error  from dual        union all        select 109   test2    Err1  from dual         SELECT distinct Name  Project  trim regexp substr str           1  level   str   FROM  SELECT Name  Project  Error str FROM temp  t CONNECT BY instr str       1  level - 1   gt  0 order by Name

User · Answer

A couple of more examples of the same   SELECT trim regexp substr  Err1  Err2  Err3            1  LEVEL   str 2 tab   FROM dual CONNECT BY LEVEL  lt   regexp count  Err1  Err2  Err3        1    SELECT trim regexp substr  Err1  Err2  Err3            1  LEVEL   str 2 tab   FROM dual CONNECT BY LEVEL  lt   length  Err1  Err2  Err3   - length REPLACE  Err1  Err2  Err3             1     Also  may use DBMS UTILITY comma to table  amp  table to comma   http   www oracle-base com articles 9i useful-procedures-and-functions-9i php DBMS UTILITY comma to table

User · Answer

Without using connect by or regexp       with mytable as         select 108 name   test  project   Err1 Err2 Err3  error from dual       union all       select 109   test2    Err1  from dual            x as         select name        project             error      error       from mytable            iter as  SELECT rownum AS pos         FROM all objects           select x name x project      SUBSTR x error        INSTR x error       1  iter pos    1        INSTR x error       1  iter pos   1 -INSTR x error       1  iter pos -1       error     from x  iter     where iter pos  lt     LENGTH x error  - LENGTH REPLACE x error         - 1

User · Answer

Here is an alternative implementation using XMLTABLE that allows for casting to different data types   select    xmltab txt from xmltable     for  text in tokenize  a b c        return  text    columns      txt varchar2 4000  path       xmltab         or if your delimited strings are stored in one or more rows of a table   select    xmltab txt from     select  a b c  inpt from dual union all   select  d e f  from dual   base inner join xmltable     for  text in tokenize  input       return  text    passing base inpt as  input    columns      txt varchar2 4000  path       xmltab   on 1 1

User · Answer

In Oracle 11g and later  you can use a recursive sub-query and simple string functions  which may be faster than regular expressions and correlated hierarchical sub-queries    Oracle Setup   CREATE TABLE table name   name  project  error   as  select 108   test     Err1  Err2  Err3  from dual union all  select 109   test2    Err1              from dual    Query   WITH table name error bounds   name  project  error  start pos  end pos   AS     SELECT name           project           error           1           INSTR  error        1     FROM   table name UNION ALL   SELECT name           project           error           end pos   2           INSTR  error        end pos   2     FROM   table name error bounds   WHERE  end pos  gt  0   SELECT name         project         CASE end pos        WHEN 0        THEN SUBSTR  error  start pos          ELSE SUBSTR  error  start pos  end pos - start pos          END AS error FROM   table name error bounds   Output     NAME   PROJECT   ERROR ---     ------    ----  108   test      Err1   109   test2     Err1   108   test      Err2   108   test      Err3     db lt  fiddle here

User · Answer

Starting from Oracle 12c you could use JSON TABLE and JSON ARRAY   CREATE TABLE tab Name  Project  Error  AS SELECT 108  test    Err1  Err2  Err3  FROM dual UNION  SELECT 109  test2   Err1              FROM dual    And query   SELECT   FROM tab t OUTER APPLY  SELECT TRIM p  AS p             FROM JSON TABLE REPLACE JSON ARRAY t Error                                  COLUMNS  p VARCHAR2 4000  PATH        s    Output    ------------------------------------------     Name    Project         Error           P       ------ --------- ------------------ ------       108    test       Err1  Err2  Err3    Err1        108    test       Err1  Err2  Err3    Err2        108    test       Err1  Err2  Err3    Err3        109    test2      Err1                Err1     ------------------------------------------    db lt  fiddle demo

User · Answer

I had the same problem  and xmltable helped me   SELECT id  trim COLUMN VALUE  text FROM t  xmltable          REPLACE text

User · Answer

REGEXP COUNT wasn t added until Oracle 11i  Here s an Oracle 10g solution  adopted from Art s solution   SELECT trim regexp substr  Err1  Err2  Err3            1  LEVEL   str 2 tab   FROM dual CONNECT BY LEVEL  lt     LENGTH  Err1  Err2  Err3       - LENGTH REPLACE  Err1  Err2  Err3                   1

User · Answer

If you have Oracle APEX 5 1 or later installed  you can use the convenient APEX STRING split function  e g   select q Name  q Project  s column value as Error from mytable q       APEX STRING split q Error       s  The second parameter is the delimiter string  It also accepts a 3rd parameter to limit how many splits you want it to perform  https   docs oracle com en database oracle application-express 20 1 aeapi SPLIT-Function-Signature-1 html GUID-3BE7FF37-E54F-4503-91B8-94F374E243E6

[sql] Splitting string into multiple rows in Oracle

Examples related to sql

Examples related to string

Examples related to oracle

Examples related to plsql

Examples related to tokenize