I have a table with a number field (non-null). I have another table with a varchar2 field. Examples:



I want to join the two tables where the number column is "in" the varchar2 column. As you can see, the UIDS column has been developed to look like the syntax of an "in" join. However, the following does not work:

select * from users join groups
where users.uid in groups.uids;

The number field cannot be in a string. I can cast the number field to a varchar 2 field, but it doesn't recognize the groups.uids column as a list of uids. It recognizes it as one value.

I believe that the next step I need to do is to strip the ( and ) from the uids value and then split it on the comma to create a list. Is that correct or is there a simpler way to merge these two tables?

  • 3
    I feel the pain of a bad database design
    – kevinskio
    Commented Jun 1, 2015 at 16:48
  • @kevinksy It isn't a database. It is two CSV files that I've loaded into Oracle. These files will change every week, so I'm trying to avoid exhaustive manipulation of the data.
    – kainaw
    Commented Jun 1, 2015 at 16:52

2 Answers 2


Not sure, why this is marked "Active yesterday". Once I'm here…

If it needs to be done (which it shouldn't, if proper database design can be applied), this could be a way:

  , Users.name AS userName
  , Groups.uids
  , Groups.name AS groupName
FROM Users
  ON INSTR(TRANSLATE(Groups.uids, '()', ',,'), ',' || TO_CHAR(Users.userid) || ',') > 0

It turns the parentheses around the list of group ids into commas to simplify the comparison against the comma padded userIDs. The latter is to prevent, e.g., 123 to match 12.

See it in action: SQL Fiddle

Please comment, if and as this requires adjustment / further detail.


To my knowledge, this cannot be done directly, but almost directly. It involves loading the data the compound field as a nested table. First, you have to create a nested table type:

create or replace type multi_uids as table of number(10,0);

Now, the table with the compound field must have a type of the nested table and declare that the nested table be stored as a nested table.

create table groups (
  uids multi_uids,
  name varchar2(20)
) nested table uids store as uids_tab;

When loading the raw data, cram the type in front of the compound string. So, the compound string might be (123,443,13), you actually insert multi_uids(123,443,13). This will store the compound field as a nested table.

New problem - how do you join the number from users on the nested table for groups? You have to cast the nested table to a table in the join.

select * from users
join groups
on users.id in (select * from table(groups.uids));

The end result is that you get a join where users.id is in groups.uids.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.