LIKE Conditional Expression

Description

The LIKE conditional expression compares patterns between character string data, and returns TRUE if a character string whose pattern matches the search word is found. Pattern comparison target domains are CHAR, VARCHAR and STRING. The LIKE search cannot be performed on an NCHAR or BIT type. If NOT comes before the LIKE keyword, the result of a NOT operation on the result of the LIKE operation is returned.

A wild card string corresponding to any character or character string can be included in the search word on the right of the LIKE operator. % (percent) and _ (underscore) can be used. .% corresponds to any character string whose length is 0 or greater, and _ corresponds to one character. An escape character is a character that is used to search for a wild card character itself, and can be specified by the user as another character whose length is 1. See below for an example of using a character string that includes wild card or escape characters.  

Syntax

expression [ NOT ] LIKE expression [ ESCAPE char]

• expression (left) : Specify the data type column of the character string. Pattern comparison, which is case-sensitive, starts from the first character of the column.
• expression (right) : Enter the search word. A character string with a length of 0 or greater is required. Wild card characters (% or _) can be included as the pattern of the search word. The length of the character string is 0 or greater.
• ESCAPE char : If the string pattern of the search word includes "_" or "%" itself, an ESCAPE character must be specified. For example, if you want to search for the character string "10%" after specifying backslash (\) as the ESCAPE character, you must specify "10\%" for the expression (right). If you want to search for the character string "C:\", you can specify "C:\\" for the expression (right).

Remark

LIKE search may not work properly for data entered in multi-byte character set environment such as utf-8. This is because byte units for string comparison operation differ depending on the character sets. You can get normal results by adding a parameter(single_byte_compare=yes) to the cubrid.conf file that enables string comparison in a single-byte units, and restarting the DB.

For details about character sets supported in CUBRID, see Definition and Characteristics. For details about the single_byte_compare parameter, see Other Parameters.

Example

--selection rows where name contains lower case 's', not upper case

SELECT * FROM condition_tbl WHERE name LIKE '%s%';

=== <Result of SELECT Command in Line 1> ===

           id  name                  dept_name                  salary

======================================================================

            3  'Jones     '          'sales'                   5400000

--selection rows where second letter is 'O' or 'o'

SELECT * FROM condition_tbl WHERE UPPER(name) LIKE '_O%';

=== <Result of SELECT Command in Line 1> ===

           id  name                  dept_name                  salary

======================================================================

            2  'Moy       '          'sales'                   3000000

            3  'Jones     '          'sales'                   5400000

--selection rows where name is 3 characters

SELECT * FROM condition_tbl WHERE name LIKE '___';

=== <Result of SELECT Command in Line 1> ===

           id  name                  dept_name                  salary

======================================================================

            1  'Kim       '          'devel'                   4000000

            2  'Moy       '          'sales'                   3000000

            5  'Kim       '          'account'                 3800000