Scenario

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.

Solution

Option 1: LIKE condition

SELECT table_name
  FROM user_tables
 WHERE LOWER(table_name) LIKE '%foo%'

The LOWER function here takes a character expression as a parameter, converts all alpha character to lower case and return.

The 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 null.
    • “_” 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')

The REGEXP_LIKE is similar to the LIKE condition, except RREGEXP_LIKE performs regular expression matching instead of the simple pattern matching performed by LIKE.

This condition complies with the POSIX regular expression standard and the Unicode Regular Expression Guidelines, for more information, refer to Oracle Regular Expression Support.

References

⤧  Previous post How to convert newlines between Unix(LF) and DOS/Windows(CRLF) ⤧  Next post [Vim] Easy Fixing File Encoding