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 (%): Use % to match any string of any length (including zero length).

  • underscore (_): Use _ to match on a single character.

To match a literal containing a % or a _, without matching other characters, the respective character in the pattern must be preceded by the escape character. The default escape character is the backslash (\) but a different character can be selected by using the ESCAPE clause.

  • The LIKE pattern can be specified using multibyte national characters. However, the optional escape character must be specified using ASCII characters.

  • The use of parameters in the ESCAPE clause is supported in 5.0.4 and later.

  • Only literals and parameters can be used in the ESCAPE clause.

Examples using LIKE

The following examples use the sample Ice hockey database.
Example 1: Find all the team members who play defense and have names starting with 'A'.
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
Example 2: Find all the team members who play defense and whose names do 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
Example 3: Find all the team members with NUMBER ending 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 LIKE with the ESCAPE clause

Create a new table with the following data.

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

Display all the data in the table test_like.

SELECT * FROM test_like;
 COL1    COL2
 ----- ---------

   3   three
   5   %five
   6   six%
   7   se%ven
   8   _eight
   9   nine_
  10   t_en
  11   \eleven
  12   twelv\e
  13   thir\teen
Example 1: Find the row with the data in col2 beginning with t, the third character as r followed by any two characters.
SELECT * FROM test_like WHERE col2 LIKE 't_r__';
 COL1  COL2
 ----- -----
   3   three
Example 2: Find the row with the data in col2 beginning with underscore (_), using the default escape character (\).
SELECT * FROM test_like WHERE col2 LIKE '\_%';
 COL1  COL2
 ----- -----
   8   _eight
Example 3: Find the row with the data in col2 beginning with the default escape character (\).
SELECT * FROM test_like WHERE col2 LIKE '\\%';
/* Use double back slash */
 COL1   COL2
 ----- -------
  11   \eleven
Example 4: Find the row with the data in col2 ending with a percent sign, using the default escape character (\).
SELECT * FROM test_like WHERE col2 LIKE '%\%';
 COL1  COL2
 ----- -----
   6   six%
Example 5: Find the row with the data in col2 beginning with a percent sign (%), using the ESCAPE clause.
SELECT * FROM test_like WHERE col2 LIKE '@%%' ESCAPE '@';

Here @ is specified as the escape character using the ESCAPE clause.

 COL1  COL2
 ----- -----
   5   %five
Example 6: Find the rows with the data in col2 containing a percent sign (%).
SELECT * FROM test_like WHERE col2 LIKE '%\%%';
 COL1   COL2
 ----- ------
   5   %five
   6   six%
   7   se%ven
Example 7: Find the rows with the data in col2 containing a percent sign (%), using the escape clause.
SELECT * FROM test_like WHERE col2 LIKE '%@%%' ESCAPE '@';
COL1   COL2
----- ------
5   %five
6   six%
7   se%ven
Example 8: Find the rows with the data in col2 containing a percent sign (%), using parameters in the LIKE expression.
SELECT * FROM test_like WHERE col2 LIKE ? ESCAPE '^';
Enter value: %^%%

 COL1  COL2
 ----- ------

  5  %five
  6  six%
  7  se%ven
Example 9: Find the rows with the data in col2 containing a percent sign (%), using parameters in the LIKE expression and the ESCAPE clause.
SELECT * FROM test_like WHERE col1 LIKE ? ESCAPE ?;
Enter value: %\%%
Enter value: \

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.

Examples using STARTING

Example 1: Find the rows with the data in the column name starting with "A".
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
Example 2: Find the rows with the data in the column name starting with "AA".
SELECT * FROM hockey WHERE name STARTING WITH 'AA';
 ID  NUMBER      NAME      POSITION   TEAM
 --- ------- ------------- --------- ------
 19    45    AARON JOHNSON  Defense  Bruins
Example 3: Find the rows with the data in the column name not starting with "F".
SELECT * FROM hockey WHERE position NOT STARTING WITH 'F';
  ID  NUMBER        NAME        POSITION   TEAM
 --- ------- ----------------- --------- ------

 15    55    JOHNNY BOYCHUK     Defense  Bruins
 16    33    ZDENO CHARA        Defense  Bruins
 17    21    ANDREW FERENCE     Defense  Bruins
 18    27    DOUGIE HAMILTON    Defense  Bruins
 19    45    AARON JOHNSON      Defense  Bruins
 20    54    ADAM MCQUAID       Defense  Bruins
 21    44    DENNIS SEIDENBERG  Defense  Bruins
 22    35    ANTON KHUDOBIN     Goalie   Bruins
 23    40    TUUKKA RASK        Goalie   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.

Examples using CONTAINING

Example 1: Find the rows with the data in the column name containing "O".
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
Example 2: Find the rows with the data in the column name containing "HAM".
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

^

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 using REGEXP

Example 1: Find the rows with the data in the column name starting with "ad".
SELECT * FROM hockey.hockey WHERE name REGEXP '^ad';
 ID  NUMBER      NAME     POSITION   TEAM
 --- ------- ------------ --------- ------
 20    54    ADAM MCQUAID  Defense  Bruins
Example 2: Find the rows with the data in the column name ending with "t".
SELECT * FROM hockey.hockey WHERE name REGEXP 't$';
 ID  NUMBER     NAME    POSITION   TEAM
 --- ------- ---------- --------- ------
 24     1    MAX SUMMIT    Fan    Bruins
Example 3: Find the rows with the data in the column name containing "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
Example 4: Find the rows with the data in the column name containing 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
Example 5: Find the rows with the data in the column name starting with "b" or "s" or ending 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
Example 6: Find if there are any rows with 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
Example 7: Find the rows with the data in the 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
Example 8: Find the rows with the data in the 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
Example 9: Find the rows with the data in the 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