Regular expressions can now also be used as a condition and as a counter.
Invantive UniversalSQL has been expanded with the SQL functions regexp_like and regexp_count. This completes the set of regular expression functions: in addition to regexp_instr, regexp_replace, and regexp_substr, regexp_like answers whether a pattern occurs, and regexp_count counts how often.
regexp_like
The function regexp_like answers whether a pattern occurs in a value. The syntax is:
regexp_like(value, pattern [, start_position [, match_parameter]])
The answer is true if the pattern occurs and false if it does not. If the value or pattern is empty, the answer is empty. The function is intended for a where clause, but can also be used as a column in a select.
regexp_count
The function regexp_count counts how often a pattern occurs in a value. The syntax is:
regexp_count(value, pattern [, start_position [, match_parameter]])
The answer is 0 if the pattern does not occur, and empty if the value or pattern is empty. The occurrences do not overlap: the search for the next occurrence continues after the end of the previous one. Thus, aa appears once in aaa, not twice.
Start position and match_parameter
Both functions have the same two optional parameters. The start position counts from 1 and defaults to 1; the search then starts at that position instead of at the beginning.
The match_parameter controls the way of comparison and accepts c, i, n, m, and x. Without this parameter, the search is case-sensitive. If both c and i are present, the last of the two applies; this was not previously the case and now works the same for all five functions.
The pattern follows the regular expression rules of Invantive UniversalSQL, described under See also for the individual functions.
Example: filter and count codes
The following example is directly executable and uses both functions simultaneously: regexp_like selects the item codes that consist of two uppercase letters, a hyphen, and four digits, and regexp_count counts the digits of those codes.
create or replace table item_codes@InMemoryStorage
as
select 'NL-1234' code
union all
select 'BE-5678' code
union all
select 'random text' code
;
select ace.code
, regexp_count(ace.code, '[0-9]') digits
from item_codes@InMemoryStorage ace
where regexp_like(ace.code, '^[A-Z]{2}-[0-9]{4}$')
order
by ace.code
The output is:
CODE DIGITS
----------- ------
BE-5678 4
NL-1234 4
The line random text does not meet the pattern and is discarded.
Example: ignore uppercase
The following example shows the difference the match_parameter makes:
select regexp_like('INVANTIVE', 'van') case_sensitive
, regexp_like('INVANTIVE', 'van', 1, 'i') insensitive
The output is:
CASE_SENSITIVE INSENSITIVE
--------------- -----------
False True
See also
Availability
The new functionality is available from release 27.0.