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 types 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 (NULL, alphabet, or number_ 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): Specifies the data type column of the character string. Pattern comparison, which is case-sensitive, starts from the first character of the column.
• expression (right): Enters 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: NULL, alphabet, or number is allowed for 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

The LIKE conditional expression is case sensitive. To disable case sensitive, use the REGEXP/RLIKE Conditional Expressions.

For details about character sets supported in CUBRID, see Definition and Characteristics.

Whether to detect the escape characters of the LIKE conditional expression is determined depending on the configuration of no_backslash_escapes and require_like_escape_character in the cubrid.conf file. For details, see Statement/Type-Related Parameters.

Note In CUBRID 2008 R4.x or lower versions, to execute string comparison operation for data entered in the multibyte charset environment such as UTF-8, the parameter setting (single_byte_compare=yes) which compares strings by 1 byte should be added to the cubrid.conf file for a successful search result. However, the versions after CUBRID 2008 R4.x support Unicode charset so the single_byte_compare parameter is no longer used.

Example

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

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

           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%';

           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 '___';

           id  name                  dept_name                  salary

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

            1  'Kim       '          'devel'                   4000000

            2  'Moy       '          'sales'                   3000000

            5  'Kim       '          'account'                 3800000