SQL Pattern Matching
NuoDB supports various types of Pattern Matching to compare data.
LIKE
| STARTING
| CONTAINING
| REGEXP
LIKE
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
clause. To match the escape character itself, including the default escape character “ESCAPE
\
”, 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
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
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
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. |
|
A single character, |
|
A single character, but neither |
|
Any of the patterns |
|
Zero or more instances of |
|
One or more instances of |
|
Either zero or one instance of |
( |
Zero or more instances of the sequence |
|
|
|
|
|
|
|
Any character that is either |
|
Any character that is neither |
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