Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

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.

LIKE search may not work properly for data entered in multi-byte character set environment such as utf-8. This is because byte unit for string comparison operation depends 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 unit, 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.

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.

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