Join to splitted string columns in Oracle

Question

I have a column in our database that holds 4 fields as a "\" delimited string.

I have split the fields as I need them seperatly in my report.

I also need to use these fields individually as where criteria against another table.

Things I've tried: Temporary table:

CREATE GLOBAL TEMPORARY TABLE pfcc
   ON COMMIT PRESERVE ROWS 
   AS select REGEXP_SUBSTR(s, '[^\]+', 1, 1) colA,
       REGEXP_SUBSTR(s, '[^\]+', 1, 2) colB,
       REGEXP_SUBSTR(s, '[^\]+', 1, 3) colC,
       REGEXP_SUBSTR(s, '[^\]+', 1, 4) colD
from (select delimited s from products
                        where productid = 1)

And then Joining this against the other table.

select * from pfcc tmp
inner join lineitems gch 
    on gch.Line = tmp.colA
    AND gch.productB = tmp.colB
    AND gch.productC = tmp.colC

I also tried to join right off without storing values in a table:

select REGEXP_SUBSTR(s, '[^\]+', 1, 1) colA,
       REGEXP_SUBSTR(s, '[^\]+', 1, 2) colB,
       REGEXP_SUBSTR(s, '[^\]+', 1, 3) colC,
       REGEXP_SUBSTR(s, '[^\]+', 1, 4) colD
from (select delimited s from products
                        where productid = 1) tmp
inner join lineitems gch 
    on gch.Line = tmp.colA
    AND gch.productB = tmp.colB
    AND gch.productC = tmp.colC

I would like to AVOID using the temporary table, and have it done similar to the second way. If this is unavoidable then so be it.

Does anyone have a solution for this?

Thanks, JFIT

Why don't you just put the pfcc as a subquery and then use that to join with your other table? — Boneist, May 12 '15 at 13:49

score 3 · Answer 1 · answered May 12 '15 at 13:48

3

You can use CTE as follows.

with pfcc as 
(select REGEXP_SUBSTR(delimited , '[^\]+', 1, 1) colA,
       REGEXP_SUBSTR(delimited , '[^\]+', 1, 2) colB,
       REGEXP_SUBSTR(delimited , '[^\]+', 1, 3) colC,
       REGEXP_SUBSTR(delimited , '[^\]+', 1, 4) colD
  from products
 where productid = 1)
select * from pfcc tmp
inner join lineitems gch 
    on gch.Line = tmp.colA
    AND gch.productB = tmp.colB
    AND gch.productC = tmp.colC;

answered May 12 '15 at 13:48

San

4,508
1
13
19

Perfect thanks for the reply - I ended up using the other answer as it fitted nicely into my massive where clause without using a temp table. Thank you. – JF it May 12 '15 at 16:11

Gordon Linoff · Accepted Answer · 2015-05-12T18:27:20.317

I think this is the query you want:

select gch.Line, gch.productB, gch.productC,
       REGEXP_SUBSTR(p.delimited, '[^\]+', 1, 4)
from  products p inner join
      lineitems gch 
      on gch.Line = REGEXP_SUBSTR(p.delimited, '[^\]+', 1, 1) and
         gch.productB = REGEXP_SUBSTR(p.delimited, '[^\]+', 1, 2) and
         gch.productC = REGEXP_SUBSTR(p.delimited, '[^\]+', 1, 3)
where p.productid = 1;

You need neither a subquery nor a temporary table.

score 2 · Answer 3 · answered May 15 '15 at 15:28

WARNING! The regular expression '[^\]+' will return unexpected results if there is a null item in the list and you are selecting the element after that null item. See this example where the 3rd item is selected, but '4' is returned. '4' is really the 4th item in the delimited list, not the third. Indeed it is the 3rd item where there is a value though.

SQL> select REGEXP_SUBSTR('1\\3\4', '[^\]+', 1, 3) colA from dual;

C
-
4

SQL>

Use this instead, where the actual 3rd item in the list is selected:

SQL> select REGEXP_SUBSTR('1\\3\4', '([^\]*)(\\|$)', 1, 3, NULL, 1) colA from dual;

C
-
3

See this post for a more detailed example and explanation: REGEX to select nth value from a list, allowing for nulls

Join to splitted string columns in Oracle

3 Answers3

Linked