Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 



REGEXP/RLIKE Conditional Expression

Description

The REGEXP and RLIKE conditional expressions 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.

Syntax

expr REGEXP|RLIKE [BINARY] pat

expr NOT REGEXP|RLIKE pat

NOT (expr REGEXP|RLIKE pat)

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

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

name

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

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

('new

line' regexp 'new

line')

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

1

 

-- ^ : match the beginning of a string

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

('cubrid dbms' regexp '^cub')

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

1

 

-- $ : match the end of a string

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

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

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

1

 

--.: match any character

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

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

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

1

 

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

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

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

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

1

 

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

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

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

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

1

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

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

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

1

 

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

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

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

0

 

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

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

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

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

1

 

-- [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]+')

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

1

 

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

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

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

1

Remark

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

and Telegraph Company or of the Regents of the University of California.

 

Permission is granted to anyone to use this software for any purpose on

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

to the following restrictions:

 

1. The author is not responsible for the consequences of use of this

software, no matter how awful, even if they arise from flaws in it.

 

2. The origin of this software must not be misrepresented, either by

explicit claim or by omission. Since few users ever read sources,

credits must appear in the documentation.

 

3. Altered versions must be plainly marked as such, and must not be

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.