SQL Pattern Matching

NuoDB supports various types of Pattern Matching to compare data.

LIKE | STARTING | CONTAINING | REGEXP

`LIKE`

Syntax

[ NOT ] LIKE pattern [ESCAPE escape_character]

Description

Generates a search for a match of the pattern specified. The search respects any collation specified for the column.

The LIKE condition allows you to use wild cards in the WHERE clause of SELECT, INSERT, UPDATE, or DELETE statements. This allows you to perform pattern matching.

The LIKE condition can be used in any valid SQL statement.

The patterns that you can choose from are:

% (percent sign) allows you to match any string of any length (including zero length)
_ (underscore) allows you to match on a single character

To match a literal containing an underscore or percent sign, without matching other characters, the respective character in pattern must be preceded by the escape character. The default escape character is the backslash “\” but a different one can be selected by using the ESCAPE clause. To match the escape character itself, including the default escape character “\”, write two escape characters.

The LIKE pattern can be specified with multi-byte national characters, except for the (optional) ESCAPE character, which must be ASCII.

Examples using `LIKE`

/* find all team members who play defense and have a name starting with M */
SELECT * FROM hockey WHERE position = 'Defense' AND name LIKE 'A%';
 ID  NUMBER       NAME      POSITION   TEAM
 --- ------- -------------- --------- ------
 17    21    ANDREW FERENCE  Defense  Bruins
 19    45    AARON JOHNSON   Defense  Bruins
 20    54    ADAM MCQUAID    Defense  Bruins

/* find all team members who play defense and name does not start with A */
SELECT * FROM hockey WHERE position = 'Defense' AND name NOT LIKE 'A%'
 ID  NUMBER        NAME        POSITION   TEAM
 --- ------- ----------------- --------- ------
 15    55    JOHNNY BOYCHUK     Defense  Bruins
 16    33    ZDENO CHARA        Defense  Bruins
 18    27    DOUGIE HAMILTON    Defense  Bruins
 21    44    DENNIS SEIDENBERG  Defense  Bruins

/* find team members with numbers that end in zero */
SELECT * FROM hockey WHERE number like '%0';
 ID  NUMBER      NAME      POSITION   TEAM
 --- ------- ------------- --------- ------
 10    20    DANIEL PAILLE  Forward  Bruins
 23    40    TUUKKA RASK    Goalie   Bruins

Examples using the ESCAPE character

CREATE TABLE test_like (col1 INTEGER, col2 STRING);
INSERT INTO test_like VALUES (3, 'three');
INSERT INTO test_like VALUES (5, '%five'), (6, 'six%'), (7, 'se%ven');
INSERT INTO test_like VALUES (8, '_eight'), (9, 'nine_'), (10, 't_en');
INSERT INTO test_like VALUES (11, '\eleven'), (12, 'twelv\e'), (13, 'thir\teen');

/* find rows beginning with t, the third character = r, and followed by two characters */
SELECT * FROM test_like WHERE col2 LIKE 't_r__';
 COL1  COL2
 ----- -----
   3   three

/* find rows beginning with underscore and use the default escape character */
SELECT * FROM test_like WHERE col2 LIKE '\_%';
 COL1  COL2
 ----- -----
   8   _eight

/* find rows beginning with the default escape character. Use double back slash */
SELECT * FROM test_like WHERE col2 LIKE '\\%';
 COL1   COL2
 ----- -------
  11   \eleven

/* find rows ending with a percent sign and use the default escape character */
SELECT * FROM test_like WHERE col2 LIKE '%\%';
 COL1  COL2
 ----- -----
   6   six%

/* find rows beginning with a percent sign */
SELECT * FROM test_like WHERE col2 LIKE '@%%' escape '@';
 COL1  COL2
 ----- -----
   5   %five

/* find rows that have a percent sign */
SELECT * FROM test_like WHERE col2 LIKE '%\%%' escape '\';
 COL1   COL2
 ----- ------
   5   %five
   6   six%
   7   se%ven

`STARTING`

Syntax

[ NOT ] STARTING [ WITH ] string

Description

Generates a search for a match using the string value. The search respects any collation specified for the column.

Example

Select names that start with a specific string value

SELECT * FROM hockey WHERE name STARTING 'A';
 ID  NUMBER       NAME      POSITION   TEAM
 --- ------- -------------- --------- ------
 17    21    ANDREW FERENCE  Defense  Bruins
 19    45    AARON JOHNSON   Defense  Bruins
 20    54    ADAM MCQUAID    Defense  Bruins
 22    35    ANTON KHUDOBIN  Goalie   Bruins

SELECT * FROM hockey WHERE name STARTING WITH 'AA';
 ID  NUMBER      NAME      POSITION   TEAM
 --- ------- ------------- --------- ------
 19    45    AARON JOHNSON  Defense  Bruins

`CONTAINING`

Syntax

[ NOT ] CONTAINING string

Description

Generates a search for a partial match of the string value. The search respects any collation specified for the column.

Example

Select names that contain a specific string value

SELECT * FROM hockey WHERE name CONTAINING 'O';

 ID  NUMBER        NAME       POSITION   TEAM
 --- ------- ---------------- --------- ------
  1    37    PATRICE BERGERON  Forward  Bruins
  2    48    CHRIS BOURQUE     Forward  Bruins
  3    11    GREGORY CAMPBELL  Forward  Bruins
  4    18    NATHAN HORTON     Forward  Bruins
 14    22    SHAWN THORNTON    Forward  Bruins
 15    55    JOHNNY BOYCHUK    Defense  Bruins
 16    33    ZDENO CHARA       Defense  Bruins
 18    27    DOUGIE HAMILTON   Defense  Bruins
 19    45    AARON JOHNSON     Defense  Bruins
 22    35    ANTON KHUDOBIN    Goalie   Bruins

SELECT * FROM hockey WHERE name CONTAINING 'HAM';
 ID  NUMBER       NAME       POSITION   TEAM
 --- ------- --------------- --------- ------
 18    27    DOUGIE HAMILTON  Defense  Bruins

`REGEXP`

Syntax

expression REGEXP pattern

Description

NuoDB supports Extended Regular Expression syntax pattern matching. This provides a more powerful means of searching by performing a pattern match of a string expression against a pattern. The REGEXP condition returns true if expression matches pattern. Otherwise, the REGEXP condition returns false. If either expression or pattern is NULL, the result is NULL. The expression can be a literal string, a string expression, or a table column. As with LIKE, the pattern characters match the expression characters exactly unless they are special characters in the regular expression language. Unlike LIKE patterns, a pattern is allowed to match anywhere within the expression, unless the pattern is explicitly anchored to the start or end of the expression.

Following is a list of special characters used in pattern matching of the REGEXP operator.

Syntax Pattern Match

Syntax	Pattern Match
^	Start of the expression
$	End of the expression
.	Any single character in the expression. Includes whitespace, tab, newline, etc.
`abc`]	A single character, `a`, `b` or `c`
`abc`]	A single character, but neither `a`, `b` nor `c`
`a`\|`b`\|`c`	Any of the patterns `a`, `b` or `c`
`a`*	Zero or more instances of `a`
`a`+	One or more instances of `a`
`a`?	Either zero or one instance of `a`
(`abc`)*	Zero or more instances of the sequence `abc`
`a`\{`n`}	`n` instances of `a`
`a`\{`n`,}	`n` or more instances of `a`
`a`\{`m`,`n`}	`m` through `n` instances of `a`. `m` must be less than or equal to `n`
`a`-`dX`]	Any character that is either `a`,`b`,`c`,`d` or `X`
`a`-`dX`]	Any character that is neither `a`,`b`,`c`,`d` nor `X`

Start of the expression

End of the expression

Any single character in the expression. Includes whitespace, tab, newline, etc.

abc]

A single character, a, b or c

abc]

A single character, but neither a, b nor c

a|b|c

Any of the patterns a, b or c

a*

Zero or more instances of a

a+

One or more instances of a

a?

Either zero or one instance of a

(abc)*

Zero or more instances of the sequence abc

a\{n}

n instances of a

a\{n,}

n or more instances of a

a\{m,n}

m through n instances of a. m must be less than or equal to n

a-dX]

Any character that is either a,b,c,d or X

a-dX]

Any character that is neither a,b,c,d nor X

Examples

/* column name starts with "ad" */``
SELECT * FROM hockey.hockey WHERE name REGEXP '^ad';``
 ID  NUMBER      NAME     POSITION   TEAM
 --- ------- ------------ --------- ------
 20    54    ADAM MCQUAID  Defense  Bruins

/* column name ends with "t" */``
SELECT * FROM hockey.hockey WHERE name REGEXP 't$';``
 ID  NUMBER     NAME    POSITION   TEAM
 --- ------- ---------- --------- ------
 24     1    MAX SUMMIT    Fan    Bruins

/* column name contains "mil" */
SELECT * FROM hockey.hockey WHERE name REGEXP 'mil';
 ID  NUMBER       NAME       POSITION   TEAM
 --- ------- --------------- --------- ------
  7    17    MILAN LUCIC      Forward  Bruins
 18    27    DOUGIE HAMILTON  Defense  Bruins

/* column name contains r, followed by any single character, followed by s */
SELECT * FROM hockey.hockey WHERE name REGEXP 'r.s';
 ID  NUMBER      NAME      POSITION   TEAM
 --- ------- ------------- --------- ------
  2    48    CHRIS BOURQUE  Forward  Bruins
  5    23    CHRIS KELLY    Forward  Bruins
 13    19    TYLER SEGUIN   Forward  Bruins
 23    40    TUUKKA RASK    Goalie   Bruins

/* column name starts with "b" or "s" or ends with "y" */``
SELECT * FROM hockey.hockey WHERE name REGEXP '^++[++bs++]++|y$';``
 ID  NUMBER       NAME      POSITION   TEAM
 --- ------- -------------- --------- ------
  5    23    CHRIS KELLY     Forward  Bruins
  9    63    BRAD MARCHAND   Forward  Bruins
 11    49    RICH PEVERLEY   Forward  Bruins
 14    22    SHAWN THORNTON  Forward  Bruins

/* zero or more instances of "ti*m" */
SELECT 'tim' REGEXP 'ti*m' FROM dual;
 ----
 TRUE

SELECT 'tiim' REGEXP 'ti*m' FROM dual;
 ----
 TRUE

SELECT'tm' REGEXP'ti*m' FROM dual;
 ----
 TRUE

/* column name has two instances of "a" */``
SELECT * FROM hockey.hockey WHERE name REGEXP 'a{2}';``
 ID  NUMBER      NAME      POSITION   TEAM
 --- ------- ------------- --------- ------
 19    45    AARON JOHNSON  Defense  Bruins

/* column name has two, three or four instances of "den" */
SELECT * FROM hockey.hockey WHERE name REGEXP 'den{2,4}';
 ID  NUMBER        NAME        POSITION   TEAM
 --- ------- ----------------- --------- ------
 21    44    DENNIS SEIDENBERG  Defense  Bruins

/* column name containing any one of x,y,z or q */
SELECT * FROM hockey.hockey WHERE name REGEXP '++[++x-zq++]++';
 ID  NUMBER        NAME       POSITION   TEAM
 --- ------- ---------------- --------- ------
  2    48    CHRIS BOURQUE     Forward  Bruins
  3    11    GREGORY CAMPBELL  Forward  Bruins
  5    23    CHRIS KELLY       Forward  Bruins
 11    49    RICH PEVERLEY     Forward  Bruins
 13    19    TYLER SEGUIN      Forward  Bruins
 15    55    JOHNNY BOYCHUK    Defense  Bruins
 16    33    ZDENO CHARA       Defense  Bruins
 20    54    ADAM MCQUAID      Defense  Bruins
 24     1    MAX SUMMIT        Fan      Bruins

SQL Pattern Matching

LIKE

Syntax

Description

Examples using LIKE

Examples using the ESCAPE character

STARTING

Syntax

Description

Example

CONTAINING

Syntax

Description

Example

REGEXP

Syntax

Description

Examples

`LIKE`

Examples using `LIKE`

`STARTING`

`CONTAINING`

`REGEXP`