Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register


REGEXP/RLIKE Conditional Expressions


The conditional expressions REGEXP and RLIKE are used interchangeably; a regular expressions is a powerful way to specify a pattern for a complex search. CUBRID uses Henry Spencer's implementation of regular expressions, which conforms the POSIX 1003.2 standards. The details on regular expressions are not described in this page. For more information on regular expressions, see Henry Spencer's regex(7).

The following list describes basic characteristics of regular expressions.

  • "." matches any single character(including new-line and carriage-return).
  • "[...]" matches one of characters within square brackets. For example, "[abc]" matches "a", "b", or "c". To represent a range of characters, use a dash (-). "[a-z]" matches any alphabet letter whereas "[0-9]" matches any single number.
  • "*" matches 0 or more instances of the thing proceeding it. For example, "xabc*" matches "xab", "xabc", "xabcc", and "xabcxabc" etc. "[0-9][0-9]*" matches any numbers, and ".*" matches every string.
  • To match special characters such as " ", " ", " ", and "\", some must be escaped with the backslash () by specifying the value of no_backslash_escapes (default: yes) to no. For details on no_backslash_escapes, see Escape Special Characters.

The difference between REGEXP and LIKE are as follows:

  • The LIKE operator succeeds only if the pattern matches the entire value.
  • The REGEXP operator succeeds if the pattern matches anywhere in the value. To match the entire value, you should use "^" at the beginning and "$" at the end.
  • The LIKE operator is case sensitive, but patterns of regular expressions in REGEXP is not case sensitive. To enable case sensitive, you should use REGEXP BINARY statement.

In the syntax below, if expr matches pat, 1 is returned; otherwise, 0 is returned. If either expr or pat is NULL, NULL is returned.

The second syntax has the same meaning as the third syntax, which both syntaxes are using NOT.





  • expr: Column or input expression
  • pat: Pattern used in regular expressions; not case sensitive

-- When REGEXP is used in SELECT list, enclosing this with parentheses is required. But used in WHERE clause, no need parentheses.

-- case insensitive, except when used with BINARY.

SELECT name FROM athlete where name REGEXP '^[a-d]';



'Dziouba Irina'

'Dzieciol Iwona'

'Dzamalutdinov Kamil'

'Crucq Maurits'

'Crosta Daniele'

'Bukovec Brigita'

'Bukic Perica'

'Abdullayev Namik'


-- : match a special character, when no_backslash_escapes=no

SELECT ('new line' REGEXP 'new



line' regexp 'new





-- ^ : match the beginning of a string

SELECT ('cubrid dbms' REGEXP '^cub');

('cubrid dbms' regexp '^cub')




-- $ : match the end of a string

SELECT ('this is cubrid dbms' REGEXP 'dbms$');

('this is cubrid dbms' regexp 'dbms$')




--.: match any character

SELECT ('cubrid dbms' REGEXP '^c.*$');

('cubrid dbms' regexp '^c.*$')




-- a+ : match any sequence of one or more a characters. case insensitive.

SELECT ('Aaaapricot' REGEXP '^A+pricot');

('Aaaapricot' regexp '^A+pricot')




-- a? : match either zero or one a character.

SELECT ('Apricot' REGEXP '^Aa?pricot');

('Apricot' regexp '^Aa?pricot')



SELECT ('Aapricot' REGEXP '^Aa?pricot');

('Aapricot' regexp '^Aa?pricot')




SELECT ('Aaapricot' REGEXP '^Aa?pricot');

('Aaapricot' regexp '^Aa?pricot')




-- (cub)* : match zero or more instances of the sequence abc.

SELECT ('cubcub' REGEXP '^(cub)*$');

('cubcub' regexp '^(cub)*$')




-- [a-dX], [^a-dX] : matches any character that is (or is not, if ^ is used) either a, b, c, d or X.

SELECT ('aXbc' REGEXP '^[a-dXYZ]+');

('aXbc' regexp '^[a-dXYZ]+')




SELECT ('strike' REGEXP '^[^a-dXYZ]+$');

('strike' regexp '^[^a-dXYZ]+$')




The following shows RegEx-Specer's license, which is library used to implement the REGEXP conditional expression.

Copyright 1992, 1993, 1994 Henry Spencer. All rights reserved.

This software is not subject to any license of the American Telephone




any computer system, and to alter it and redistribute it, subject

to the following restrictions:







credits must appear in the documentation.



misrepresented as being the original software. Since few users

ever read sources, credits must appear in the documentation.


4. This notice may not be removed or altered.