CODEWITHSUNDEEP
sqloracle-database
Sarath
Sarath

Reputation: 39

Existance of 3 words in a string in oracle query

I want to check whether 3 specific words exists in a column or not using an Oracle query.

For example my column value is: 'Google Earth lets you fly anywhere on Earth to view satellite imagery, maps, terrain, 3D buildings, from galaxies in outer space to the canyons of the ocean'.

I want to check whether the three words Earth, galaxies and buildings exist in the string.

How I can do this in an Oracle query?

Upvotes: 0

Views: 319

Answers (4)

Okdel
Okdel

Reputation: 183

Use regexp:

WITH tmp AS
  (
    SELECT 'Earth, galaxies and buildings' str FROM dual UNION ALL
    SELECT 'Earth, buildings and galaxies' str FROM dual UNION ALL
    SELECT 'Earth2, galaxies and buildings' str FROM dual UNION ALL
    SELECT 'Earth , galaxies and buildings' str FROM dual UNION ALL
    SELECT 'Earth,galaxies,buildings' str FROM dual UNION ALL
    SELECT 'Earthgalaxiesbuildings' str FROM dual UNION ALL
    SELECT 'earth, galaxies and buildings' str FROM dual
  )
SELECT
  str
FROM
  tmp
WHERE
  REGEXP_LIKE(UPPER(str), '([[:punct:][:space:]]|^)EARTH([[:punct:][:space:]]|$)') AND
  REGEXP_LIKE(UPPER(str), '([[:punct:][:space:]]|^)GALAXIES([[:punct:][:space:]]|$)') AND
  REGEXP_LIKE(UPPER(str), '([[:punct:][:space:]]|^)BUILDINGS([[:punct:][:space:]]|$)')

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

You want to look for words only probably. So when looking for 'space' you don't want to find, say, 'respaced'. Use REGEXP_LIKE with word boundaries:

select *
from mytable 
where regexp_like(text, '(^|\W)earth(\W|$)', 'i')
  and regexp_like(text, '(^|\W)galaxies(\W|$)', 'i')
  and regexp_like(text, '(^|\W)buildings(\W|$)', 'i');

Upvotes: 3

Himanshu
Himanshu

Reputation: 91

"Earth" "Earth," should be selected as word as per logic. Using '%Earth% will also become true for words like "Un-Earth" or "Earthing" and you don't want it.

So,

where (upper(col) like upper('% earth %') OR upper(col) like upper('% earth.%') OR upper(col) like upper('% earth,%') ) AND
  (upper(col) like upper('% galaxies %') OR upper(col) like upper('% galaxies.%') OR upper(col) like upper('% galaxies,%')) AND
  upper(col) like upper('% buildings %') OR upper(col) like upper('% buildings.%') OR upper(col) like upper('% buildings,%'))

based on how much data is corrupt, you can add multiple condition inside OR.

Upvotes: 0

David Oneill
David Oneill

Reputation: 13105

Use something like this in the where clause (if you want to be exact about the case):

where col_name like '%Earth%' 
and col_name like '%galaxies%' 
and col_name like '%buildings%'

as @Tim pointed out in the comments, if you want to ignore case, you can by using upper() or lower():

where upper(col_name) like '%EARTH%'
and upper(col_name) like '%GALAXIES%'

etc.

Upvotes: 1

Related Questions