Spliting data in oracle table

Recently I was working on re-modelling and re-designing an oracle database. As part of the re-modelling I was required to import data from old tables to new tables.

One of the table column in old table used to store data as colon (:) delimited value e.g. BEACH:CYCLE:BBQ etc. In the front end APEX and check box group is used to fetch the data from that table column. In the new table column data need to be splitted and record as an individual activity e.g. BEACH.

Here is what I have used to insert data into new tables, however I had to change the condition to retrieve only specific activity.

SELECT sit_id, SUBSTR (activity_type, 1, INSTR (ACTIVITY_TYPE, ':', 2) - 1)
  FROM TBL_SITES
 WHERE ACTIVITY_TYPE LIKE '%' || 'BBQ' || '%'

Here is the result I get –

32	BBQ
33	BBQ
35	BBQ
36	BBQ
39	BBQ
426	BBQ
499	BBQ
503	BBQ
563	BBQ

I had to tweak my code a little bit, as it was not displaying BBQ when the position is not at the beginning.

SELECT sit_id,
       CASE
          WHEN    SUBSTR (activity_type,
                          1,
                          INSTR (activity_type, ':', 1) - 1)
                     IS NOT NULL
               OR SUBSTR (activity_type,
                          1,
                          INSTR (activity_type, ':', 1) - 1)
                     IS NULL
          THEN
             'FISHING'
       END
  FROM tbl_sites
 WHERE ACTIVITY_TYPE LIKE '%' || 'FISHING' || '%'

Now I know all returning rows have ‘FISHING’ value so I just inserted it.

Here is the insert script –

 There could be an easier way to do this instead of changing the ACTIVITY_TYPE condition many times depending on how many activity types you have. In my case it was 8 only :).

Here is another simple way to do this –

/*  Simple insert just setting the value of the field as of condition */

INSERT INTO TBL_SITE_ACTIVITY (SIT_ID, ACTIVITY_NAME)
   SELECT sit_id, 'ABSEILING'
     FROM TBL_SITES
    WHERE ACTIVITY_TYPE LIKE '%'
|| 'ABSEILING' || '%'