Imran Hemani Imran Hemani - 9 days ago 7
SQL Question

Using between in the SQL IN clause

Is there any way possible to use a range in the IN clause of sql.

I have a scenario where I have the values in the column like:

BIN_1_1
111111 - 222222


The user enters a value, eg; 111134, and I need to check if the value entered lies within the range in the column value.

Is there any way to use IN statement and use between in it ?

FOR i in (select * from V_CUS_SEG_BIN_RANGE) LOOP
IF v_input1 IN
(REGEXP_SUBSTR(i.bin_1_1, '[^-]+', 1, 1), REGEXP_SUBSTR(i.bin_1_1, '[^-]+', 1, 2)
THEN
dbms_output.put_line('Duplicate!');
END LOOP;


I get the values separated with RegEx, but can I use between to compare if the values lies in between the range.

Answer

You can use the results of your REGEXP_SUBSTR calls in a BETWEEN condition, as in:

IF v_input1 BETWEEN REGEXP_SUBSTR(i.bin_1_1, '[^-]+', 1, 1)
                AND REGEXP_SUBSTR(i.bin_1_1, '[^-]+', 1, 2) OR
   v_input1 BETWEEN REGEXP_SUBSTR(i.bin_1_2, '[^-]+', 1, 1)
                AND REGEXP_SUBSTR(i.bin_1_2, '[^-]+', 1, 2) OR
   v_input1 BETWEEN REGEXP_SUBSTR(i.bin_1_3, '[^-]+', 1, 1)
                AND REGEXP_SUBSTR(i.bin_1_3, '[^-]+', 1, 2)
THEN
   ...whatever...
END IF;

Best of luck.

Comments