0

I have a column with less than <50 comma separated values. And the number of comma separated values in that particular column is not constant I would like to query this table with IN/AND/OR clause for these comma separated values:

So I would like to:

  • split these values into separate columns in the select query
  • Query for a person's market code with AND, OR and IN :
    • EUP and APCAC
    • APAC or EU Or CA
    • IN ( APAC,EU,LATIM)

I have to use this query in spring data jpa native query

I don't want to use as many substr.

I thought to accomplish this by using connect by with level would be useful after referring the following answers - ans1 and ans2

Usertable:

username market_code in_use
john eup,apac,Latim 0
sebastin apac,Latim 0
xavier ca,apac,Latim 0

However the following only returns one row where I expected to have 3 rows :

select regexp_substr(market_code,'[^|]+', 1, level) from testautomadm.userpool
where  AND USERNAME = 'john'
connect by regexp_substr('market_code', '[^|]+', 1, level)  
is not null

Any help to solve this use case much appreciated.

Thank you!

deepakguna
  • 145
  • 3
  • 16

1 Answers1

1

You can use the multiset and hierarchy query as follows:

Select user_name, 
       listagg(market_code, ',') within group (order by pos) as market_code,
       In_use
  From
(Select user_name, 
       regexp_substr(market_code, '[^,]+',1, column_value) as market_code, 
       in_use,
       column_value as pos
  From t,
       table(cast(multiset(
         select level from dual 
         connect by level <= length (regexp_replace(t.market_code, '[^,]+'))  + 1
        ) as sys.OdciNumberList)) levels) t
  Where market_code in ('EUP','APCAC')
  Group by user_name, in_use
  Having count(distinct market_code) = 2

Above query shows the usage of AND as we have used count = 2 You can use count = 1 and respective market_code in IN for OR And IN query

Popeye
  • 35,427
  • 4
  • 10
  • 31