Using between in the SQL IN clause

Question

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.

This would be much easier if 111111 and 222222 were in separate columns of INT. As it is, anything could be in a column that allows the value 111111 - 222222, which makes many methods of solving this very fragile. — Michael McGriff
– Michael McGriff, Commented Nov 23, 2016 at 17:33

Bob Jarvis - Слава Україні · Accepted Answer · 2016-11-23 18:27:05Z

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.

I have multiple of bin columns - bin_1_2, bin_1_3 so on. do I separate it with OR ?

Collectives™ on Stack Overflow

Using between in the SQL IN clause

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related