Oracle SQL select, compare 2 listagg’s values

I have two tables: one is TEXT_MSG with column ROLES, another one is USERS with column ROLES. Columns ROLES is listagg VARCHAR2, separator is : symbol. It is possible to write SQL statement with WHERE clause to get records from USERS table WHERE one or many roles from TEXT_MSG.ROLES equals role/s from USERS.ROLES? For example:

TEXT_MSG.ROLES is 1:5:7:10;
USERS.ROLES is 1:3.

Without PL/SQL using?

Answer

You can generate the records according to U.ROLES and compare them one by one as follows:

SELECT distinct U.USERID, U.ROLES, T.ROLES FROM USERS U 
CROSS JOIN table( 
        cast(multiset( 
            select level from dual 
            connect by level <= regexp_count(U.ROLES, ':') + 1) 
        as sys.odcinumberlist) ) lvls
JOIN TEXT_MSG T ON ':' || T.ROLES || ':' LIKE 
                   '%:' || REGEXP_SUBSTR(U.ROLES,'[^:]+',1,lvls.column_value) || ':%'