CODEWITHSUNDEEP
sqlstringoracle-databasesearchplsql
MAndrews
MAndrews

Reputation: 69

Oracle - Search for Word within a Word

I'm hoping there is a brain out there that would kind enough to help a lost soul;

I have two columns; COL_A and COL_B, each record for both columns only contains one word (the length of the word can vary).

What I'm trying to do is search COL_A and identify a partial (word) match with COL_B, for example, COL_A = 'MSOFT', COL_B = 'MICROSOFT' therefore this would be classified as a match.

Likewise, if COL_A = 'RANGE' and COL_B = 'ORANGE' this would also be classified as a match.

However, if COL_A = 'ORGAN' and COL_B = 'ORANGE' this would not be classified as a match.

I'm open to suggestions (pure SQL, Function, etc.).

As always, any help would be much appreciated.

Many thanks in advance!

Upvotes: 1

Views: 2303

Answers (3)

Valli
Valli

Reputation: 1450

Something like this.. which will work for your sample data

SELECT *
  FROM yourtable
WHERE INSTR(col_a,col_b,1)>0
   OR INSTR(col_b,col_a,1)>0
   or INSTR(substr(col_a,2,length(col_a)), col_b,1)>0
   or INSTR(substr(col_b,2,length(col_b)), col_a,1)>0

Upvotes: 0

user5683823
user5683823

Reputation:

Here is a simplistic way to solve this. It's not pretty, and it is probably not efficient (but the problem itself may not have very efficient solutions, by its nature). It should be easy to read, understand and maintain though.

I assume NULL in col_a is treated as "empty string" and therefore it matches col_b regardless of what is in col_b. If instead you want to treat it as an actual NULL, you can either return 'N' or perhaps, better yet, NULL, in the MATCH column.

with
     inputs ( col_a, col_b ) as (
       select 'MSOFT', 'MICROSOFT' from dual union all
       select 'RANGE', 'ORANGE'    from dual union all
       select 'BLUES', 'BLUES'     from dual union all
       select 'ORGAN', 'ORANGE'    from dual union all
       select 'ALMA' , 'KALIMERA'  from dual union all
       select null   , 'OCTOPUS'   from dual union all
       select 'ALPHA', 'ALPHABET'  from dual
     )
-- End of simulated inputs (for testing only, not part of the solution).
-- SQL query begins BELOW THIS LINE. Use your actual table and column names.
select col_a, col_b,
       case when col_a is null then 'Y'
            when exists ( select level from dual
                          where col_a = substr( col_b, 1, level - 1 ) || 
                                          substr( col_b, -(length(col_a) - level + 1),
                                                           length(col_a) - level + 1 )
                          connect by level <= length(col_a) + 1
                        )
                               then 'Y'
                               else 'N' end as match
from  inputs;

COL_A COL_B     M
----- --------- -
MSOFT MICROSOFT Y
RANGE ORANGE    Y
BLUES BLUES     Y
ORGAN ORANGE    N
ALMA  KALIMERA  N
      OCTOPUS   Y
ALPHA ALPHABET  Y

Upvotes: 1

Mureinik
Mureinik

Reputation: 311316

A simple like condition should do the trick:

SELECT *
FROM   mytable
WHERE  col_a LIKE '%' || col_b || '%'

Upvotes: 0

Related Questions