Splitting Comma Separated string into columns by using REGEXP_SUBSTR

oracleoracle-11g

Here is my example string:

select 'po1,qty1,po2,qty2,..,POn,QTYn' str from dual.

The string length is dynamic. It may have more than one PO and QTY.

Now, I want the output displayed as all the POs in first column and quantities in
second column.

Best Answer

select
  regexp_substr(pq, '[^,]+', 1, 1) as po,
  regexp_substr(pq, '[^,]+', 1, 2) as qty
from
(
  select
    regexp_substr('po1,qty1,po2,qty2','[^,]+,[^,]+', 1, level) as pq
  from dual
    connect by regexp_substr('po1,qty1,po2,qty2','[^,]+,[^,]+', 1, level) is not null
);


PO         QTY
---------- ----------
po1        qty1
po2        qty2

Related Solutions

Dividing comma separated strings into columns

Is this what you're looking for? I'm using regexp_substr

with test as
(select '1,2,3' col1, 'Ab,cd,ef' col2 from dual)
  select regexp_substr(col1, '[^,]+', 1, rownum) result1, regexp_substr(col2, '[^,]+', 1, rownum) result2
  from test
  connect by level <= length(regexp_replace(col1, '[^,]+')) + 1;

This is the SQL Fiddle.

Oracle – Selecting Multiple Columns from Subquery with Connect By

ORA-00913 too many values

Cause: The SQL statement requires two sets of values equal in number. This error occurs when the second set contains more items than the first set. For example, the subquery in a WHERE or HAVING clause may return too many columns, or a VALUES or SELECT clause may return more columns than are listed in the INSERT.
Action: Check the number of items in each set and change the SQL statement to make them equal.

In your case your subquery is returning two columns to the first query expecting one column. You can change your query as follow.

SELECT  folder_name name,
  document_count,
  (SELECT SUM(e2.document_count)
    FROM       folder_mapping e2      START WITH e2.folder_mapping_id       = e1.folder_mapping_id CONNECT BY prior e2.folder_mapping_id = e2.parent_folder_mapping_id
  )
  sum_sal, (SELECT SUM(e2.document_count)
    FROM       folder_mapping e2      START WITH e2.folder_mapping_id       = e1.folder_mapping_id CONNECT BY prior e2.folder_mapping_id = e2.parent_folder_mapping_id
  ) folder_count_total
FROM
  folder_mapping e1

That was just the fix for your error. Actually you can use the following query to get the desired output which is better(In terms of cost) than the original query.

SQL> desc folder_mapping;
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FOLDER_MAPPING_ID                  NUMBER
 FOLDER_NAME                        VARCHAR2(20)
 FOLDER_FILENET_ID                  NUMBER
 PARENT_FOLDER_MAPPING_ID               NUMBER
 FOLDER_COUNT                       NUMBER
 DOCUMENT_COUNT                     NUMBER
 SHARED_FLAG                        NUMBER
 ATTACHMENT_FLAG                    NUMBER

SQL> select * from folder_mapping;

FOLDER_MAPPING_ID FOLDER_NAME          FOLDER_FILENET_ID
----------------- -------------------- -----------------
PARENT_FOLDER_MAPPING_ID FOLDER_COUNT DOCUMENT_COUNT SHARED_FLAG ATTACHMENT_FLAG
------------------------ ------------ -------------- ----------- ---------------
        4 root3                  111
               2        2          2           0           0

        1 root                    11
               0        2          3           0           0

        2 root1                  111
               1        2         33           0           0



SQL> SELECT folder_name, SUM(document_count) "Total_Doc_Count", SUM(folder_count) "Total_Folder_Count"  FROM(
   SELECT CONNECT_BY_ROOT folder_name as folder_name, document_count, folder_count
      FROM folder_mapping
      CONNECT BY PRIOR folder_mapping_id = parent_folder_mapping_id)
      GROUP BY folder_name;   

FOLDER_NAME      Total_Doc_Count Total_Folder_Count
-------------------- --------------- ------------------
root1                 35              4
root                  38              6
root3                  2              2

SQL>

Best Answer

Related Solutions

Dividing comma separated strings into columns

Oracle – Selecting Multiple Columns from Subquery with Connect By

ORA-00913 too many values

Related Question