help with like query - table.column like '%table.column%' - ?

[TWillard]

Is there a way to use a like operator and both sides of the like equation are columns. Listed below are two examples of what I am thinking. Is there a way to achieve this logic in a standalone query, without having to write a script or procedure?

select count(*)
from table_a a
where a.message like '%( select b.message from table_b where rownum < 2 )%'
;

select count(*)
from table_ a, table_b b
where a.message like '%b.message%'
;

 

[taupirho]

Nearly, you need to modify both to be

select count(*)
from table_a a
where a.message like '%' || ( select b.message from table_b where rownum < 2 ) || '%'

select count(*)
from table_ a, table_b b
where a.message like '%' || b.message || '%'



In order to understand recursion, you must first understand recursion.

 

[SantaMufasa]

TWillard,

Your specifications are a bit hazy. If you have the following data:

select * from table_a;

MESSAGE
--------------------------
This is message 1
This is message 2
This is message 3
This is message 4
This is string 5
This is string 6
This is an innocuous entry

7 rows selected.

select * from table_b;

MESSAGE
-----------------
message
string

2 rows selected.

Do you expect to see as output:

  COUNT(*)
----------
         6

1 row selected.

...or would you rather see something like:

MESSAGE   COUNT(*)
------- ----------
message          4
string           2

In either case, I recommend using Oracle's "INSTR()" function instead of the "LIKE" comparison operator:

select count(*)
  from table_a a
      ,table_b b
 where instr(a.message,b.message) > 0
/

  COUNT(*)
----------
         6
or,

select b.message, count(*)
  from table_a a
      ,table_b b
 where instr(a.message,b.message) > 0
 group by b.message
/

MESSAGE   COUNT(*)
------- ----------
message          4
string           2

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]