REGEXP_LIKE

FUNCTION REGEXP_LIKE(IN text CLOB, IN pattern CHAR(N), [ IN options CHAR(3)]); RETURNS BOOLEAN;

Parametry

text

text, that is compared with the regular expression

pattern

regular expression

options

options affecting the work with the regular expression

Popis

This function searches the text character string for a part described by the regular expression pattern.

This function is built on the PCRE (Perl-Compatible Regular Expression) library corresponding with the PERL program version 5.8.

THe regular expression must be specified according to the PERL language rules:

a dot represents any character,
an asterisk represents any count (even zero) of iterations of a previous element
a plus represents any (non-zero) count of iterations of a previous element
a question mark represents an optional occurrence of a previous element
parenthesis delimit an element, that will be affected with the following asterisk, plus or question mark
brackets allow you to define the set of allowed characters
braces define the count of iterations of a previous element
a vertical line separates alternatives,
a backslash before a character with a special function makes it a normal character
\w,\W,\d,\D,\s,\S represent character groups (alphanumeric, non-alphanumeric, numbers, non-numbers, empty character, non-empty character). These groups are not working, if the text is a unicode string (type NCLOB) with national characters. Use the more general sequences \p and \P in this case.
\b represents the word margin (beginning and end). The same restrictions as for \b are valid.

The ^ and $ character on the beginning and end of a regular expression allow to "anchor" it, so the regular expression doesn't have to cover the whole extent of the text from the beginning to end. Hence the function may search for a substring, prefix, suffix or the equality of the whole text with the regular expression.

If the text of the regular expression are not in Unicode, it is assumed, that the regular expression can be converted into the text charset.

If the options parameter is specified, it contains characters that alter the regular expression behaviour:

the 'i' character disables case-sensitivity;
the 'n' character enables the dot metacharacter to represent also the next line pass;
the 'm' character forces the ^ and $ metacharacter to limit the beginning and end of the line, not the whole text.

Searching with the regular expression is fast, because the text is always searched in linear time no matter how complex the expression is. Searching with the regular expression condition doesn't use indexes and therefore leads to searching one text after another. This is not very effective method for vast database texts, a good alternative is the use of fulltext tools.

The REGEXP_LIKE function is more flexible and effective than the LIKE function.

Návratová hodnota

Returns the result of comparing the text with the regular expression - TRUE if they're corresponding, FALSE otherwise. The function returns UNKNOWN if the regular expression syntax is incorrect.

Příklad

SELECT * FROM Txtab
WHERE Regexp_like(name, 'A(be|braham|\.) +Lincoln', 'i')

This query selects thos records from the Txtab table, that have the 'Abe Lincoln', 'Abraham Lincoln' or 'A. Lincoln' in the Name column (there may be more spaces between both words). This query is not case-sensitive.

Příklad

SELECT company FROM Addresses
WHERE REGEXP_LIKE(company,'[0-9]+');
//WHERE REGEXP_LIKE(company,'[0-9]{1,}');
//WHERE REGEXP_LIKE(company,'[[:digit:]]+');
//WHERE REGEXP_LIKE(company,'\d+');

This query selects those companies, that have at least on digit in their name. Valid names: 'Software602 a.s.', 'TELE3', 'VÜ 1444', 'O2'. 4 equivalent notations of the regular expression are used in the example.