Help using regular expressions

oracleregular expression

I have two ORACLE tables.

First table:

ID    l_name f_name belong_to

123   Smith  John        A Club

456   Jones  Mary        The Club, Your Club, My Club

789   Moore  Sue         The Club, A Club, Some Club

007   Wills  Art         My Club, Some Club, Two Club

Second table:

Club_ID  Name

1            A Club

2            The Club

3            Your Club

4            My Club

5            Some Club

6            Two Club

The idea is to link these tables based on the name of the club in the 'name' field of the second table AND have a row of output showing the person's name with each club he or she belongs to, like this:

l_name    f_name    belong_to

Smith     John      A Club

Jones     Mary      The Club

Jones     Mary      Your Club

Jones     Mary      My Club

Moore     Sue       The Club

Moore     Sue       A Club

Moore     Sue       Some Club

Wills     Art       My Club

Wills     Art       Some Club

Wills     Art       Two Club

The belong_to field in the first table can have multiple clubs separated by ', ' (i.e. comma space). 

SELECT distinct id, l_name, f_name, trim(regexp_substr(belong_to, '[^,]+', 1)) club_str
FROM first_table

So far, this only returns the first club a person belongs to in each record of the first table.

Best Answer

As Mark Sinkinson said storing comma separated list in the table is the bad idea.Reason there is a better structure to store list that is 'TABLE' :).

Instead you can create third table to store belonging club information. However you can get your result using the following query.

SELECT tbl1.id,
TRIM(REGEXP_SUBSTR(tbl1.belongto, '[^,]+',1, lines.column_value)) Belongto
FROM tbl1,
TABLE(CAST(MULTISET(SELECT level FROM dual CONNECT BY INSTR(tbl1.belongto,',',1, level-1)>0) AS SYS.odciNumberList))lines;

And look at the plan of the above query its quite expensive.:(

| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU) |Time     |

--------------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      | 16336 |   430K|    65  (11)| 00:00:01 |

|   1 |  NESTED LOOPS               |      | 16336 |   430K|    65  (11)| 00:00:01 |

|   2 |   TABLE ACCESS FULL         | TBL1 |     2 |    50 |     2   (0)| 00:00:01 |

|   3 |   COLLECTION ITERATOR SUBQUERY FETCH|      |  8168 | 16336 |    32  (13)| 00:00:01 |

|*  4 |    CONNECT BY WITHOUT FILTERING     |      |       |       ||      |

|   5 |     FAST DUAL               |      |     1 |       |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

So consider to change your design for betterment.
Thanks

Related Question