Oracle select from string (varchar2)

If you receive string with list of ID separated in some way as input, and you should use this string as filer, you have few ways do this.

1. Dynamic SQL

declare
  ids varchar2(100) := '1,2,3,4,5';
  l_cnt number;
begin
  execute immediate 'select count(*) from account where account_id in (' || ids || ')' into l_cnt;
  dbms_output.put_line(l_cnt);
end;

Minuses - dynamic SQL should use when you can't do your task without dynamic sql, too large performance impact in bad side. Dynamic SQL is way to the Dark side.

2. Check is ID in string

declare
  ids varchar2(100) := '1,2,3,4,5';
  l_cnt number;
begin
  select count(*)
    into l_cnt
    from trader_account
   where instr(ids || ',', account_id || ',') != 0;
  dbms_output.put_line(l_cnt);
end;

Minuses - not used index by table. In his situation will be fast index scan (INDEX FAST FULL SCAN) because all data needed for this select already in index, so Oracle will use index instead of table, but in another select where not all data will be in index, Oracle will use full table scan TABLE ACCESS FULL

3. Transform string into datasource

declare
    ids   varchar2(100) := '1,2,3,4,5';
    l_cnt number;
begin
    select count(*)
      into l_cnt
      from account
     where account_id in
           (select TO_NUMBER(trim(SUBSTR(t1.str,
                                         t1.curr_pos + 1,
                                         DECODE(t1.next_pos,
                                                0,
                                                LENGTH(t1.str) + 2,
                                                t1.next_pos)
                                          - t1.curr_pos - 1)))
              from (select ids str,
                           DECODE(level,
                                  1,
                                  0,
                                  INSTR(ids, ',', 1, level - 1)
                                  ) as curr_pos,
                           INSTR(ids, ',', 1, level) as next_pos
                      from dual
                    connect by level <= LENGTH(ids)
                                        - LENGTH(
                                            replace(ids,
                                              ',',
                                              '')
                                          ) + 1) t1);
    dbms_output.put_line(l_cnt);
end;

Pluses - index will be used

Minuses - max length of string (SQL 4000, PL/SQL 32768)

4. Transform array of strings into datasource.

declare
    ids1  varchar2(100) := '1,2,3,4,5';
    ids2  varchar2(100) := '7,8,9,10,11,12';
    l_cnt number;
begin
    with tmp_str as
     (select 1 as id, ids1 as ids
        from dual
      union
      select 2 as id, ids2 as ids from dual),
    tmp_str2 as
     (select t.*, length(ids) - LENGTH(replace(ids, ',', '')) + 1 as cnt from tmp_str t),
    tmp_dual as
     (select level as lv from dual connect by level <= (select max(cnt) as total_cnt from tmp_str2)),
    tmp_str3 as
     (select *
        from (select id, lv, ids, cnt, row_number() over(partition by id order by lv) as rn from tmp_dual, tmp_str2) t1
       where rn <= cnt)
     
    select count(*)
      into l_cnt
      from account
     where account_id in
           (select TO_NUMBER(trim(SUBSTR(t1.str,
                                         t1.curr_pos + 1,
                                         DECODE(t1.next_pos, 0, LENGTH(t1.str) + 2, t1.next_pos) - t1.curr_pos - 1)))
              from (select ids str,
                           DECODE(lv, 1, 0, INSTR(ids, ',', 1, lv - 1)) as curr_pos,
                           INSTR(ids, ',', 1, lv) as next_pos
                      from tmp_str3) t1);
    dbms_output.put_line(l_cnt);
end;

When you have more than one string for filtering you can use last way. Algorithm can be easy modified for your datasource (pl/sql table, array...)

Last comment

Name* E-mail

Code*