Number field in varchar2 field

oracle

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

Users
UID,Name
123,Mickey
41128,Joe
43,Alice

Groups
UIDS,Name
(123,43),Children
(43),Admins
(),Deleted

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?

Best Answer

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:

SELECT
  Users.userID
  , Users.name AS userName
  , Groups.uids
  , Groups.name AS groupName
FROM Users
INNER JOIN Groups
  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.

Related Question