SQL using LIKE operator with multiple values from a table

Question

I have a list of numbers that I keep in a table named A. I want to search these numbers in another table named K. In this table named K, I am trying to search a column that is named PAR and this column contains values like A and also other values that contains characters and numbers.

For example; my table A has a number:
12312312312
Table K column PAR has something like
#NUM=12312312312#NA=45453#085937.....

table A contains 20k data. I am trying to find if table K column PAR includes that 20k values. How can I do that?

I use TOAD and ORACLE SQL. I don't have grant to use PL/SQL. Thanks!

Answer 1

Here's one option, which splits par into rows (that contain numeric values only) and joins that subquery results with a table's num column (with the to_char function applied to it, to avoid implicit datatype conversion and false positive results).

Sample data:

SQL> WITH
  2     a (num)
  3     AS
  4        (SELECT 12312312312 FROM DUAL
  5         UNION ALL
  6         SELECT 1231 FROM DUAL
  7         UNION ALL
  8         SELECT 85937 FROM DUAL),
  9     k (par)
 10     AS
 11        (SELECT '#NUM=12312312312#NA=45453#085937' FROM DUAL
 12         UNION ALL
 13         SELECT '#XY=12319' FROM DUAL
 14         UNION ALL
 15         SELECT '#FM=1185937#NUM=1231#12310' FROM DUAL)

Query:

 16  SELECT x.par, a.num
 17    FROM a
 18         JOIN (
 19                 SELECT PAR, REGEXP_SUBSTR (par,
 20                                       '\d+',
 21                                       1,
 22                                       COLUMN_VALUE) kpar
 23                   FROM k
 24                        CROSS JOIN
 25                        TABLE (
 26                           CAST (
 27                              MULTISET (    SELECT LEVEL
 28                                              FROM DUAL
 29                                        CONNECT BY LEVEL <= REGEXP_COUNT (par, '=') + 1)
 30                                 AS SYS.odcinumberlist))
 31              ) x
 32            ON TO_CHAR (a.num) = x.kpar;

PAR                                           NUM
-------------------------------- ----------------
#NUM=12312312312#NA=45453#085937      12312312312
#FM=1185937#NUM=1231#12310                   1231

SQL>

---

P.S. You said

I dont have grant to use PL/SQL

There's no such grant. You might not be granted to create PL/SQL named program units (functions, procedures, triggers, packages), but you certainly can create an anonymous PL/SQL block.

Answer 2

Maybe you Could use simple InStr() function to see if there is a number contained in some string. Something like here:

WITH 
    A AS
        (Select 1 "ID", 12312312312 "PAR" From Dual Union All
         Select 2 "ID", 14576 "PAR" From Dual),
    K AS
        (Select 1 "ID", '#NUM=12312312312#NA=45453#085937.....' "PAR" From Dual Union All
         Select 2 "ID", '#NUM=56756756756#NA=45453#085937.....' "PAR" From Dual)
Select To_Char(A.PAR) "PAR", Case When InStr(K.PAR, '#NUM=' || To_Char(A.PAR) || '#NA') = 0 Then 'Not Exists' Else 'Exists' End "EXISTS" 
From A
Inner Join K ON(K.ID = A.ID)

--  PAR                                      EXISTS    
--  ---------------------------------------- ----------
--  12312312312                              Exists    
--  14576                                    Not Exists

You should include some delimiters too like '#NUM=123123... ...' or anything else that will ensure exact match through the rows of your data. This is just something to think about.
The InStr() function could be used in any part of Select command - either in selection list or join or where condition. It is much faster with big datasets than RegExp...