Help using regular expressions
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