sql - Search if number is contained within an expression like: 1-3,5,10-15,20

Question

In an oracle database-table I need to find a result for a given lot-number. The field where lot-numbers are saved is a string containing something like '1-3,5,10-15,20' (the numbers inside this string are sorted)

is there a way of doing this?

in the example above, the result should be found for the following lot-numbers:

1,2,3,5,10,11,12,13,14,15,20

There is no way to do it in the application, so it has to be done inside the databse.

something like: "SELECT * FROM products WHERE lot = 2"

Answer 1

It is possible to do this all in SQL by use of the REGEXP_SUBSTR function and hierarchical queries:

with list_of_ids as (
select regexp_substr(a, '[[:digit:]]+',1, 1) as lot1
     , nvl( regexp_substr(a, '(-)([[:digit:]]+)',1, 1, 'i', '2')
          , regexp_substr(a, '[[:digit:]]+',1, 1)) as lot2
  from (select regexp_substr('1-3,5,10-15,20' , '[^,]+', 1, level) as a
          from dual
       connect by regexp_substr('1-3,5,10-15,20' , '[^,]+', 1, level) is not null
               )
       )
select a.*
  from products a
  join list_of_ids b
    on a.lot between b.lot1 and b.lot2

However, I must emphasise that normalising your database properly is the way to go. This solution may not scale well and does a hugely unnecessary amount of work.

It works like this:

First split your data on the comma:

SQL>  select regexp_substr('1-3,5,10-15,20', '[^,]+', 1, level) as a
  2     from dual
  3  connect by regexp_substr('1-3,5,10-15,20', '[^,]+', 1, level) is not null
  4          ;

A
--------------
1-3
5
10-15
20

Next, split it on the hyphen to provide a minimum and maximum lot to use in the BETWEEN before finally joining it to the table. The NVL is there to ensure that there is always a maximum.

SQL> select regexp_substr(a, '[[:digit:]]+',1, 1) as lot1
  2       , nvl( regexp_substr(a, '(-)([[:digit:]]+)',1, 1, 'i', '2')
  3             , regexp_substr(a, '[[:digit:]]+',1, 1)) as lot2
  4    from (select regexp_substr('1-3,5,10-15,20' , '[^,]+', 1, level) as a
  5            from dual
  6         connect by regexp_substr('1-3,5,10-15,20' , '[^,]+', 1, level) is not null
  7                 )
  8         ;

LOT1           LOT2
-------------- --------------
1              3
5              5
10             15
20             20

SQL>

Here's a working SQL Fiddle with the full query.