How do I search IN condition from column?

Question

I have create two tables and named it ROLES and ACCESS_CONTROL. Each ACCESS_CONTROL user can have many ROLES in ALLOWABLE_ROLES separated by comma.

ROLES

ROLE_ID     ROLE_NAME   
P001        ROLE 1      
P002        ROLE 2      
P003        ROLE 3      
P004        ROLE 4      
P005        ROLE 5      
P006        ROLE 6      
P007        ROLE 7      
P008        ROLE 8      
P009        ROLE 9      
P010        ROLE 10     
P011        ROLE 11     
P012        ROLE 12

ACCESS_CONTROL

USER_ID     USER        ALLOWABLE_ROLES                 
A001        MASTER      P009,P011,P012,P010,P006,P005,P001      
A002        ADMIN 1     P010,P011,P012,P003,P004,P006,P008      
A003        ADMIN 2     P011,P012,P010,P006             
A005        ADMIN 3     P011,P012,P003,P007,P006,P009,P001,P005     
A006        USER 1      P011,P012,P001,P006             
A007        USER 2      P010,P011,P012,P003,P006,P001,P000,P002

I try to get list of ROLES that a user have by change the column ALLOWABLE_ROLES value from this P009,P011,P012,P010,P006,P005,P001 to this 'P009','P011','P012','P010','P006','P005','P001' using CONCAT in SQL.

SELECT
    ROLE_ID,
    ROLE_NAME
FROM
    ROLES
WHERE
    ROLE_ID IN (SELECT CONCAT('''',CONCAT(RTRIM(CHAR(REPLACE(ALLOWABLE_ROLES,',',''','''))),'''')) FROM ACCESS_CONTROL WHERE USER_ID = 'A001')

However result returns zero.

But when I chance the code to this,

SELECT
    ROLE_ID,
    ROLE_NAME
FROM
    ROLES
WHERE
    ROLE_ID IN ('P009','P011','P012','P010','P006','P005','P001')

the result output shows

RESULT

ROLE_ID     ROLE_NAME   
P001        ROLE 1      
P005        ROLE 5      
P006        ROLE 6      
P009        ROLE 9      
P010        ROLE 10     
P011        ROLE 11     
P012        ROLE 12

Is there any possible to get output directly from the column?

Have you checked the value of `SELECT CONCAT('''',CONCAT(RTRIM(CHAR(REPLACE(ALLOWABLE_ROLES,',',''','''))),''''))` — flyingfox, Oct 06 '18 at 05:12
Yes, I have run the query and it's output is: 'P009','P011','P012','P010','P006','P005','P001' — Mohd Faizal Mohd Zahari, Oct 06 '18 at 05:45
You should avoid typing - even just the title - in ALL CAPS. This is (a) annoying, because it's hard to read, and (b) it's considered SHOUTING at your audience, which is really just rude. — marc_s, Oct 06 '18 at 07:08
Ideally you should _normalise_ your database design https://en.m.wikipedia.org/wiki/First_normal_form — Paul Vernon, Oct 06 '18 at 07:56
Fix the data model! SQL is not designed to store multiple values in a string. It is designed to store multiple values in separate rows in a table. — Gordon Linoff, Oct 06 '18 at 12:48

Mark Barinstein · Accepted Answer · 2018-10-06T08:45:05.317

1

SELECT R.*
FROM ROLES R
JOIN ACCESS_CONTROL A ON LOCATE(','||R.ROLE_ID||',', ','||A.ALLOWABLE_ROLES||',')<>0
WHERE A.USER_ID = 'A001'

+1 To Paul's comment.

edited Oct 06 '18 at 08:45

answered Oct 06 '18 at 08:33

Mark Barinstein

11,456
2
8
16

score 0 · Answer 2 · answered Oct 06 '18 at 08:41

In MSSQL 2017 you can use STRING_SPLIT function:

SELECT ROLE_ID, ROLE_NAME FROM ROLES IN STRING_SPLIT WHERE ROLE_ID IN (select value from STRING_SPLIT(select top 1 ALLOWABLE_ROLES where USER_ID = 'A001'))

But, for before MS2016 you can use the function in which has been described on How to split a comma-separated value to columns

How do I search IN condition from column?

2 Answers2