This predicate allows for regular expression matching. The exact syntax that NexusDB implements diverges from the SQL standard.

 

Syntax

 

  <row value predicand> [ NOT ] SIMILAR [ TO ] <similar pattern> [ IGNORE CASE ]

 

Usage

 

The regular expressions are evaluated using the PCRE library.

 

A quick overview of the supported syntax can be found at

https://www.pcre.org/original/doc/html/pcresyntax.html

 

The full specification of valid patterns can be found at

https://www.pcre.org/original/doc/html/pcrepattern.html

 

SIMILAR evaluates to TRUE if the pattern evaluated against the predicand produces one or more matches, it evaluates to FALSE of there are 0 matches, if predicand or pattern is NULL, it returns NULL.

 

Here is an example of what is now possible:

 

CREATE LOCAL TEMPORARY TABLE #strings (

  string NVARCHAR(30)

);

 

INSERT INTO #strings VALUES ('the blue king');

INSERT INTO #strings VALUES ('the Red king');

INSERT INTO #strings VALUES ('the white prince');

INSERT INTO #strings VALUES ('the white queen');

INSERT INTO #strings VALUES ('red queen');

 

SELECT

  *,

  string SIMILAR TO 'the (red|white) (king|queen)',

  string SIMILAR TO 'the (red|white) (king|queen)' IGNORE CASE,

  string SIMILAR TO '(red|white) (king|queen)'

FROM

  #strings;

 

The produced output is:

the blue king - FALSE FALSE FALSE

the Red king - FALSE TRUE FALSE

the white prince - FALSE FALSE FALSE

the white queen - TRUE TRUE TRUE

red queen - FALSE FALSE TRUE