Oracle SQL : Intersect tuple literals

Question

Is there a function or operator or otherwise simple(r) construct to obtain the intersection of two tuple literals in oracle sql?

Taking into consideration the following example:

Having the following table

------------------------------
TABLE sometable
------------------------------
id | telephone | mobile | fax
------------------------------
 1 | 123       | 456    | 789

Given a list of n numbers { n1, n2, n3, ... , n } find id, such that:

   telephone = n1 or mobile = n1 or fax = n1
or telephone = n2 or mobile = n2 or fax = n2
or telephone = n3 or mobile = n3 or fax = n3
....
or telephone = n  or mobile = n  or fax = n 

Two plausible solutions are:

1. Solution 1

SELECT id FROM sometable
WHERE
   n1 IN (telephone, mobile, fax)
OR n2 IN (telephone, mobile, fax)
OR n3 IN (telephone, mobile, fax)
....
OR n  IN (telephone, mobile, fax)
;

2. Solution 2

SELECT id FROM sometable
WHERE
   telephone IN (n1, n2, n3, ..., n)
OR mobile    IN (n1, n2, n3, ..., n)
OR fax       IN (n1, n2, n3, ..., n)
;

However is there a function / operator to do the following?

SELECT id
FROM sometable
WHERE
 intersect_function
 (
    (telephone, mobile, fax),
    (n1, n2, n3, ..., n)
 )
 = TRUE
;

An alternative, simpler construct would be welcomed, taking into consideration that this condition is part of a longer query with more numerous and possibly more complex conditions.

Thanks.

Answer 1

My idea is to convert your search numbers into a table via the with clause:

Then with a little regexp trick you can create, from a single row, one row per value, and match them against your table thanks to in clause:

create TABLE sometable
(
  id number, 
  telephone number, 
  mobile number, 
  fax number
);

insert into sometable values(1, 123, 456, 789);
insert into sometable values(2, 0, 0, 123);
insert into sometable values(3, 456, 0, 0);

with w(n) as
(
  select regexp_substr('123, 456', '\d+', 1, level) n
  from dual
  connect by regexp_instr('123, 456', '\d+', 1, level) != 0
)
select *
from sometable s, w
where w.n in (s.telephone, s.mobile, s.fax)
;

This gives as expected:

ID  TELEPHONE  MOBILE   FAX  N
1   123        456      789  123
2   0          0        123  123
1   123        456      789  456
3   456        0        0    456