How to create a view spliting one column to 2 or more using a regular expression?

Question

We have a non normalized table that contains foreign key infomration as free text inside a column.

I would like to create a view that will transform and normalize that table.

E.g. a column that contains the following text:

"REFID:12345, REFID2:67890"

I want to create a view that will have REFID1 and REFID2 as 2 separate integer columns.

Is that possible in Oracle? what are the aproaches, and what is the best performing one.

Answer 1

Oracle10g+ supports regular expressions; you could use REGEXP_SUBSTR:

SELECT REGEXP_SUBSTR(text, '[:digit:]{5}', 1, 1) AS first_refid,
       REGEXP_SUBSTR(text, '[:digit:]{5}', 1, 2) AS second_refid
  FROM TABLE

Answer 2

If the order of the REFID/REFID2 tags might change, you could use this:

WITH d AS (
   SELECT 'REFID:12345, REFID2:67890' id_list FROM DUAL
   UNION ALL
   SELECT 'REFID2:111, REFID:222' FROM DUAL
)
SELECT id_list
      ,CASE WHEN INSTR(refid,',') > 0
            THEN SUBSTR(refid, 1, INSTR(refid,',')-1)
            ELSE refid
       END AS refid
      ,CASE WHEN INSTR(refid2,',') > 0
            THEN SUBSTR(refid2, 1, INSTR(refid2,',')-1)
            ELSE refid2
       END AS refid2
FROM (
   SELECT id_list
         ,SUBSTR(id_list
                ,INSTR(id_list,'REFID:')+6
                ) AS refid
         ,SUBSTR(id_list
                ,INSTR(id_list,'REFID2:')+7
                ) AS refid2
   FROM d
);