How to tokenize semicolon separated column value to pass to IF statement in a function in Oracle DB

I have a table called ‘config’ and when I query it in following manner:

SELECT value FROM config WHERE property = 'SPECIAL_STORE_ID'

its response will be: 59216;131205;76707;167206 //... (1)

I want to tokenize the above values using semicolon as the delimiter and then use them in a user-defined Function’s IF statement to compare, something like this:

IF in_store_id exists in (<delimited response from (1) above>)//...(2)
THEN do some stuff

where in_store_id is the parameter passed-in to the function

Is this possible to do as one-liner in (2) above ?

I’m on Oracle 12c

Answer

One-liner? I don’t think so, but – if you’re satisfied with something like this, fine.

SQL> select * From config;

VALUE          PROPERTY
-------------- ----------------
7369;7499;7521 SPECIAL_STORE_ID

SQL> declare
  2    in_store_id varchar2(20) := 7369;
  3    l_exists    number;
  4  begin
  5    select instr(value, ';' || in_store_id || ';')
  6      into l_exists
  7      from config
  8      where property = 'SPECIAL_STORE_ID';
  9
 10    if l_exists > 0 then
 11       dbms_output.put_line('that STORE_ID exists in the value');
 12    else
 13       dbms_output.put_line('that STORE_ID does not exist in the value');
 14    end if;
 15  end;
 16  /
that STORE_ID exists in the value

PL/SQL procedure successfully completed.

SQL>