0

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!

8
  • 1
    The LIKE condition you are looking for is probably k.par like '%#NUM=' || a.num || '#%'. Commented Oct 3, 2023 at 11:04
  • 1
    @Thorsten, that will return false positives if any substring of K.PAR contains A.NUM. I guess Sinergy would want to match complete values. Commented Oct 3, 2023 at 11:06
  • Yep, just corrected this. Commented Oct 3, 2023 at 11:06
  • Just noticed it, @Thorsten. Though, there are also "#NA" indicators, as well as no indicator at all (the last value in sample string). Commented Oct 3, 2023 at 11:07
  • @ThorstenKettner Seems to be '#' || k.par || '#' like '%=' || a.num || '#%' or k.par || '#' like '%#' || a.num || '#%' Commented Oct 3, 2023 at 11:07

2 Answers 2

1

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...

Sign up to request clarification or add additional context in comments.

2 Comments

Worth pointing that instr() looking for 12312312312 would also match on #NUM=8888123123123128888#NA=45453#085937.
@APC True, that's why delimiters (if there are any) should be used.
0

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.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.