Link to home
Start Free TrialLog in
Avatar of Vinuth Kumar
Vinuth KumarFlag for India

asked on

Retrive values based on comma sepearted

I have table

 USER_ID  ROLE_ID  
    1            11,32,44
    2             34, 11, 32, 44
    3             11
    4             44, 22, 32
   5             32
  6              32, 44


If i pass 11, 32 role_id in where condition then it should fetch column which contains 11 and 32 , only 11 or only 32 should not be fetched , whatever role_id  i pass all should be present, please help me with query oracle , it will really helpful for me
Avatar of Helena Marková
Helena Marková
Flag of Slovakia image

If I correctly understand your requirement it can be something like this:
select * from your_table a where instr(a.ROLE_ID,'11')<>0 and instr(a.ROLE_ID,'32')<>0;

It is only a tip I think that there are also another solutions :).
Avatar of Vinuth Kumar

ASKER

but exactly how many roleIds will be unkonown, it depends upon user, so 10 differnt role Id i may get and i should fetch depending on that
ASKER CERTIFIED SOLUTION
Avatar of Geert G
Geert G
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi geert i do have user table and user roles table, but when admin assigns multiple roles then i will stored in that manner which i have retirve for some other purpose
their i finding it difficult sir
thanx for your valuable time you spent sir
Thanks for your valuable suggestions guys
Thanx a lot geert, indeed its a bad design, we decided to handle in front end instead in query, your feedback save us cracking our head and wasting time