Search code examples
sqloraclesql-like

oracle sql 'like' to find a specific substring

Using oracle sql, I want to find a text containing, for example, the exact word 'SAN' or its plural if any. So my attempt was this:

select * 
from table a
where upper(a.text_field) like '%SAN%'

But one of the results from the query was any text containing 'san' such as:

artisan
partisan

But the desired results should be:

abcde san fgeft 
san abcde
abcde san(s) <- if there is a plural form

How do I find the exact word in a text in oracle sql?

Thank you.

Solution

  • I prefer regexp, like this

    select * 
from table a
where regexp_like(a.text_field,'\wsan\w', 'i')

    \w - means word boundary, i - case unsensetive

    But pure SQL could be with all word boundaries permutations, for example:

    select * 
from table a
where upper(a.text_field) like '% SAN %' or upper(a.text_field) like 'SAN %' or upper(a.text_field) like '% SAN' --- and so on