HSQLDB - Regular Expressions

HSQLDB supports some special symbols for pattern matching operation based on regular expressions and the REGEXP operator.

Following is the table of pattern, which can be used along with REGEXP operator.

Example

Let us try different example queries to meet our requirements. Take a look at the following given queries.

Try this Query to find all the authors whose name starts with ^A .

SELECT author FROM tcount_tbl WHERE REGEXP_MATCHES(author, ^A.* );

After execution of the above query, you will receive the following output.

+-----------------+
|     author      |
+-----------------+
|     Abdul S     |
|    Ajith kumar  |
+-----------------+

Try this Query to find all the authors whose name ends with ul$ .

SELECT author FROM tcount_tbl WHERE REGEXP_MATCHES(author, .*ul$ );

After execution of the above query, you will receive the following output.

+-----------------+
|     author      |
+-----------------+
|    John Poul    |
+-----------------+

Try this Query to find all the authors whose name contains th .

SELECT author FROM tcount_tbl WHERE REGEXP_MATCHES(author, .*th.* );

After execution of the above query, you will receive the following output.

+-----------------+
|     author      |
+-----------------+
|    Ajith kumar  | 
|     Abdul S     |
+-----------------+

Try this query to find all the authors whose name starts with vowel (a, e, i, o, u).

SELECT author FROM tcount_tbl WHERE REGEXP_MATCHES(author, ^[AEIOU].* );

After execution of the above query, you will receive the following output.

+-----------------+
|     author      |
+-----------------+
|     Abdul S     |
|    Ajith kumar  |
+-----------------+