I'm trying to find multiples numbers inside a string.

For exemple, I would like to get the rows id which contain STRING1(100,201) AND STRING2(1,4)

Results : ID 1 and ID 2

Thank you very much for your help ! Kind regards, Max

CodePudding user response：

Here is one way - keeping things organized for easier maintenance. I assume Oracle 12.1 or higher, so I can use JSON functions (to do quick work of splitting lists); in older versions, you would have to write the function differently, but the concept would be the same.

The idea is to write a function that splits lists of numbers and returns a nested table instead. You need a global (schema level) type, so I define that first, and then the function. Then I show a sample table (the one you shared with us) and how the query would look like.

Create schema-level type and helper function

create or replace type tbl_of_num is table of number;
/

create or replace function str_to_tbl(s varchar2)
  return tbl_of_num
  deterministic
is
  pragma udf;
  ton tbl_of_num;
begin
  select cast(collect(val) as tbl_of_num)
    into ton
    from json_table('[' || s || ']', '$[*]' columns val number path '$');
  return ton;
end;
/

Create small table for testing

create table t (id, string1, string2) as
    select 1, '100,101,201', '1,2,3,4'  from dual union all
    select 2, '100,103,201', '1,4,9,10' from dual union all
    select 3, '101,102,200', '1,3,4,10' from dual
;

Sample query

First I define two bind variables, and I initialize them. This is how you would do it in SQL*Plus; you may have other ways to pass values to bind variables (depending on your application, user interface, etc.)

variable string1 varchar2(100)
variable string2 varchar2(100)

exec :string1 := '100,201'; :string2 := '1,4'

QUERY AND OUTPUT:

select id
from   t
where  str_to_tbl(:string1) submultiset of str_to_tbl(string1)
  and  str_to_tbl(:string2) submultiset of str_to_tbl(string2)
;


        ID
----------
         1
         2

CodePudding user response：

This is a horrible data model. One method uses like:

select t.*
from t
where ',' || string1 || ',' like '%,100,%' and
      ',' || string1 || ',' like '%,201,%' and
      ',' || string2 || ',' like '%,1,%' and
      ',' || string2 || ',' like '%,4,%' ;