CODEWITHSUNDEEP
sqloracle-databasesql-like
Brooke
Brooke

Reputation: 435

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.

Upvotes: 1

Views: 678

Answers (1)

Ilia Maskov
Ilia Maskov

Reputation: 1898

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

Upvotes: 1

Related Questions