oracle sql 'like' to find a specific substring

Question

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.

Ilia Maskov · Accepted Answer · 2015-05-02 18:21:59Z

1

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

answered May 2, 2015 at 18:15

Ilia Maskov

1,91816 silver badges27 bronze badges

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

1 Answer 1