Imran Hemani Imran Hemani - 10 months ago 69
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:

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)

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

Answer Source

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)

Best of luck.