convert a comma separated VARCHAR to be used in an IN clause in PL/SQL?

Question

I have a comma-separated VARCHAR determined dynamically.

varchar cHighRank := (1,2,3,4,5,6,7,8)

I would like to use this in the following IN Clause, but the system produces an error since an IN clause is only for integers:

if (rank in cHighRank) then
    --do the high rank...
elsif (rank in cLowRank) then
    -- do the low rank
end if;

I have to break a list of integers into parts. It could be 16, 12, etc. But I don't know how many since it is dynamic.

Lowrank: 1,2,3,4,5,6,7,8
HighRank: 9,10,11,12,13,14,15,16

How can I convert a comma-separated VARCHAR to be used in an IN clause?

score 1 · Answer 1 · answered Sep 10 '13 at 19:08

Not sure about MySQL, but with Oracle, you could use a regex check instead of the IN clause, with some work on border cases

If REGEXP_LIKE(cHighRank, "rank" + ",") or REGEXP_LIKE(cHighRank, "," + "rank") or REGEXP_LIKE(cHighRank, "(" + "rank" + ")")

Should be possible with MySQL as well, only I haven't worked with regexes there

score 1 · Answer 2 · answered Sep 10 '13 at 19:17

One way to check whether an "item" is in a comma separate list is to use the INSTR function.

Assuming you don't have any extra spaces in the list, one trick is to add a leading and trailing comma e.g.

',1,2,3,'

And then search for a given element such as ',2,'

DECLARE 
   cHighRank VARCHAR2(100) := '1,2,3,4,5,6,7,8';

BEGIN
  IF INSTR( ','||cHighRank||',' , ','||rank||',' ) > 0 THEN
     -- matched
  END IF;

score 1 · Answer 3 · answered Sep 11 '13 at 05:42

declare
  v_ranks constant varchar2(32767):= '1,2,3,4,5,6,7,8,9,10,11';
  -- number of ranks is number of commas + 1
  v_number_of_ranks constant number := regexp_count(v_ranks, ',') + 1;
  -- find the middle point
  -- you definition how to split odd number of ranks to hi/low might differ
  v_pos constant number := instr(v_ranks, ',', 1, v_number_of_ranks / 2);
begin
  -- split around the middle point
  dbms_output.put_line(' lowrank: ' || substr(v_ranks, 0, v_pos));
  dbms_output.put_line('highrank: ' || substr(v_ranks, v_pos + 1));
end;
/

Output:

 lowrank: 1,2,3,4,5,6,
highrank: 7,8,9,10,11

score 1 · Answer 4 · answered Sep 11 '13 at 08:59

DECLARE
    Lowrank  VARCHAR2(30) := '1,2,3,4,5,6,7,8';
    HighRank VARCHAR2(30) := '9,10,11,12,13,14,15,16';
    rank     VARCHAR2(30) := '16';
BEGIN
    IF REPLACE(REGEXP_INSTR(Lowrank, ',{0,1}' || rank || ',{0,1}'), ',') > 0 THEN
        DBMS_OUTPUT.PUT_LINE('Lowrank');
    ELSIF REPLACE(REGEXP_INSTR(HighRank, ',{0,1}' || rank || ',{0,1}'), ',') > 0 THEN
        DBMS_OUTPUT.PUT_LINE('HighRank');
    END IF;
END;

convert a comma separated VARCHAR to be used in an IN clause in PL/SQL?

4 Answers4