SQL Commands: LIKE and ILIKE
Published on March 3, 2022 by Kevin
The predicates LIKE and ILIKE are used to search for strings that match
a given pattern, so you can search or for a single word (or string) in a
long text field.
LIKE is case sensitive, ILIKE is case insensitive.
expression [NOT] LIKE pattern [ESCAPE esc-character] expression [NOT] ILIKE pattern [ESCAPE esc-character]
Two special characters are used to construct the comparison.
- % The percent sign indicates that any number of characters (or none
at all) can be located in the place occupied by the %.
- _ The underscore sign indicates that any single character can be
located in the place occupied by the _.
Comparing empty strings always evaluates TRUE.
The result is FALSE if the expression contains a NULL.
If you want to find a string containing a percent sign or the underscore
character (for example, “% discount”), use an ESCAPE character before
the % or _. The ESCAPE character indicates that the % or _ is to be
taken literally. The syntax is shown below. (The expression in the
syntax selects “_T”.):
SELECT... WHERE... LIKE '%+_T%' ESCAPE '+'
You can choose what character to use as the escape character—this
example uses the plus sign (+), which precedes the underscore. Example 3
uses the equals sign (=) and then the hash (#) as ESCAPE characters.
Note: “The ANSI ‘92 standard states that the expression and pattern
arguments for LIKE can be string value expressions. However, the
Kognitio implementation limits the pattern argument to be a constant.”
Example 1- Using the Percent Character %
This example uses both LIKE and NOT LIKE to obtain suppliers from
Smalltown with a postcode that doesn’t start with ST1. Both the postcode
and the town name come from s_address column:
SELECT * FROM supplier WHERE LOWER(s_address) LIKE '%smalltown%' AND UPPER(s_address) NOT LIKE 'ST1 %' ORDER BY s_suppkey
This example obtains partkeys, containers and names of all types of
‘pumps’ in the PARTS table:
SELECT p_partkey, p_container, p_name FROM part WHERE p_name ILIKE '%PUMP%' ORDER BY 1
Example 2 – Using LIKE with the Underscore Character _
There are customers in the CUSTOMER table with names like Bert Browne
and Burt Brown. You can use LIKE with the underscore character to choose
both spellings of Burt/Bert:
SELECT * FROM customer WHERE UPPER(c_name) LIKE 'B_RT%' ORDER BY 1
Example 3: Using an Escape Character
To see how the escape character works, search for suppliers with a
comment that includes “5%”:
SELECT s_name, s_comment FROM supplier WHERE s_comment LIKE '%5=%%' ESCAPE '=' ORDER BY 1
Now change the search to find comments with “% discount”, and use # as
the escape character:
SELECT s_name, s_comment FROM supplier WHERE s_comment LIKE '%#% discount%' ESCAPE '#' ORDER BY s_name