Select query where a column contains set of numbers

Question

I have to write a query on a table which has a varchar column. Value in this column may have a numbers as substring

Lets possible say the column values are

Data
-----------------------
abc=123/efg=143/ijk=163
abc=123/efg=153/ijk=173

now I have to query the table where data contains the numbers [123,143,163] but shouldnt contain any other number.

How can I write this select query ?

Thorsten Kettner · Answer 1 · 2016-05-24T07:54:51.763

This looks like a very bad database design. If you are interested in separate information stored in a string, then don't store the string but the separate information in separate columns. Change this if possible and such queries will become super simple.

However, for the time being it's easy to find the records as described, provided there are always three numbers in the string as in your sample data. Add a slash at the end of the string, so every number has a leading = and a trailing /. Then look up the numbers in the string with LIKE.

select *
from mytable
where data || `/` like '%=123/%'
  and data || `/` like '%=143/%'
  and data || `/` like '%=163/%';

If these three numbers are in the string, then all numbers match. Hence there is no other number not matching.

If there can be more numbers in the string but no duplicates, then count equal signs to determine how many numbers are in the string:

select *
from mytable
where data || '/' like '%=123/%'
  and data || '/' like '%=143/%'
  and data || '/' like '%=163/%'
  and regexp_count(data, '=') = 3;

And here is a query accepting even duplicate numbers in the string:

select *
from mytable
where regexp_count(data, '=') >= 3
  and regexp_count(data, '=') =
      regexp_count(data || '/', '=123/') +
      regexp_count(data || '/', '=143/') +
      regexp_count(data || '/', '=163/');

score 0 · Answer 2 · edited May 23 '17 at 11:44

Oracle Setup:

CREATE TABLE table_name ( data ) AS
SELECT 'abc=123/efg=143/ijk=163' FROM DUAL UNION ALL
SELECT 'abc=123/efg=153/ijk=173' FORM DUAL;

Then you can create some virtual columns to represent the data:

ALTER TABLE table_name ADD abc GENERATED ALWAYS AS (
  TO_NUMBER( REGEXP_SUBSTR( data, '(^|/)abc=(\d+)(/|$)', 1, 1, NULL, 2 ) )
) VIRTUAL;
ALTER TABLE table_name ADD efg GENERATED ALWAYS AS (
  TO_NUMBER( REGEXP_SUBSTR( data, '(^|/)efg=(\d+)(/|$)', 1, 1, NULL, 2 ) )
) VIRTUAL;
ALTER TABLE table_name ADD ijk GENERATED ALWAYS AS (
  TO_NUMBER( REGEXP_SUBSTR( data, '(^|/)ijk=(\d+)(/|$)', 1, 1, NULL, 2 ) )
) VIRTUAL;

And can add appropriate indexes if you want:

CREATE INDEX table_name__abc_efg_ijk__idx ON table_name( abc, efg, ijk );

Query:

Then if you are only going to have those three keys you can do:

SELECT abc, efg, ijk
FROM   table_name
WHERE  abc = 123
AND    efg = 143
AND    ijk = 163;

However, if you could get more than three keys and want ignore additional values then you could do:

CREATE TYPE intlist AS TABLE OF INT;
/

SELECT *
FROM   table_name
WHERE  INTLIST( 143, 123, 163 )
       =
       CAST(
         MULTISET(
           SELECT TO_NUMBER(
                    REGEXP_SUBSTR(
                      t.data,
                      '[^/=]+=(\d+)(/|$)',
                      1,
                      LEVEL,
                      NULL,
                      1
                    )
                  )
           FROM   DUAL
           CONNECT BY LEVEL <= REGEXP_COUNT( t.data, '[^/=]+=(\d+)(/|$)' )
         )
         AS INTLIST
       );

This has the added bonus that INTLIST(123, 143, 163) can be passed as a bind parameter (depending on the client program you are using and the Oracle driver) so that you can simply change how many and what numbers you want to filter for (and that the order of the values does not matter).

Also, if you want it to contain at least those values then you can change INTLIST( ... ) = to INTLIST( ... ) SUBMULTISET OF.

Select query where a column contains set of numbers

2 Answers2