Usage

expression [NOT] LIKE pattern [ESCAPE esc-character]
expression [NOT] ILIKE pattern [ESCAPE esc-character]

Notes

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