Fuzzy text searching and case insensitive matching in Oracle database
Fuzzy text searching with case-insensitive matching.
The default behavior of
LIKE and the other comparison operators such as
= are case-sensitive, unlike PostgresSQL has a nice
ILIKE keyword (typically a bit slower), work with Oracle database requires a little bit of effort to get this job done.
SELECT table_name FROM user_tables WHERE LOWER(table_name) LIKE '%foo%'
LOWER function here takes a character expression as a parameter, converts all alpha character to lower case and return.
LIKE condition allows you to use wildcards to specify a test involving pattern matching. Whereas the equality operator (
=) exactly matches one character value to another.
- Wildcard character
The wildcard character is used to substitute one or more characters in a string:
%” the percent sign can match 0 or more characters, except
_” the underscore sign in the pattern matches exactly 1 character.
Option 2: REGEXP_LIKE Condition
SELECT table_name FROM user_tables WHERE regexp_like(table_name, 'foo', 'i')
REGEXP_LIKE is similar to the
LIKE condition, except
RREGEXP_LIKE performs regular expression matching instead of the simple pattern matching performed by
This condition complies with the POSIX regular expression standard and the Unicode Regular Expression Guidelines, for more information, refer to Oracle Regular Expression Support.
- REGEXP_LIKE Condition - SQL Language Reference from Oracle Documentation
- Oracle Regular Expression Support - Oracle Documentation