Regular Expressions

This article is about using regular expression in sql queries. You can also use Regular Expressions in Filters within the GUI.

SQLite supports regular expressions in the where clause of a query. regexp is a reserved word as part of the language syntax but it is not quite "out of the box", as it requires a function to be hooked at run-time.

DB Browser for SQLite (DB4S) provides this hooked function using a standard regular expression engine from Qt, the GUI framework on which DB4S is built, to provide a rich Perl-like pattern matching syntax.

The following query will return every Product where the Description ends with "Kg" or "kg" (the regexp specifies either of those strings at the end, signified by the "$").

    select * from Products where Description regexp '(kg|Kg)$'

DB4S supports these modifiers as a prefix to the regexp pattern: (?modifiers)pattern

• i case insensitive
• m multi-line mode
• s . matches \n
• U swap meaning of greediness modifiers syntax

Examples:

SELECT * FROM test WHERE something REGEXP '(?i)el'; -- case insensitive

This query will return every match beginning of string(^)

SELECT name FROM student WHERE name REGEXP '^sa'; -- Gives all the names starting with ‘sa’.Example- sam,samarth.

This query will return every match ending with string($)

SELECT name FROM student WHERE name REGEXP 'on$'; -- Gives all the names ending with ‘on’.

This query will return every match which contain any character listed between the square brackets([abc]):

SELECT name FROM student_tbl WHERE name REGEXP '[jz]'; -- Gives all the names containing ‘j’ or ‘z’.Example – Lorentz, Rajs.

Useful references:

• https://www.sqlite.org/lang_expr.html#regexp - Official SQLite documentation
• http://en.wikipedia.org/wiki/Regular_expression - Handy reference and introduction

Version Notes:

• DB Browser for SQLite supports regexp modifier syntax (?modifiers)pattern starting with version 3.12.
• If you get the message "no such function: regexp" you may have checked "Disable Regular Expression extension" on the Extensions tab of the Preferences dialog, or you are using a version of DB4S prior to 3.6,(released in April 2015).