Regular expressions in SQLite

Regular expressions are probably the most powerful text processing tool without programming.

SQLite does not support regular expressions by default. However, you can easily enable them using the sqlean-regexp extension.

Note. Unlike other DBMS, adding extensions to SQLite is a breeze. Download a file, run one database command — and you are good to go.

Features • Pattern syntax • Installation and usage

Pattern matching and text replacement

There are three main tasks people usually solve using regular expressions:

Match a string against the pattern.
Extract a part of the string that matches the pattern.
Replace all parts of the string that match the pattern.

sqlean-regexp provides a separate function for each of these tasks.

regexp_like

Checks if the source string matches the pattern.

select regexp_like('Meet me at 10:30', '\d+:\d+');
-- 1
select regexp_like('Meet me at the cinema', '\d+:\d+');
-- 0

1
0

regexp_substr

Returns a substring of the source string that matches the pattern.

select regexp_substr('Meet me at 10:30', '\d+:\d+');
-- 10:30

select regexp_substr('Meet me at 17:05', '\d+:\d+');
-- 17:05

10:30
17:05

regexp_replace

Replaces all matching substrings with the replacement string.

select regexp_replace('password = "123456"', '"[^"]+"', '***');
-- password = ***

select regexp_replace('1 2 3 4', '[2468]', 'even');
-- 1 even 3 even

password = ***
1 even 3 even

Pattern syntax

sqlean-regexp supports pretty advanced syntax, including various groups, lazy quantifiers, and look-arounds:

select regexp_substr('the year is 2020', '(\d{2})\1');
-- 2020
select regexp_substr('the year is 2021', '(\d{2})\1');
-- (null)

select regexp_substr('1 2 3 2 4 5', '.*2');
-- 1 2 3 2
select regexp_substr('1 2 3 2 4 5', '.*?2');
-- 1 2

select regexp_substr('new year', '(\w+)\s(?=year)');
-- new
select regexp_substr('last year', '(\w+)\s(?=year)');
-- last

2020

1 2 3 2
1 2
new
last

Installation and usage

Download the latest release
Use with SQLite command-line interface:

sqlite> .load ./regexp
sqlite> select regexp_like('abcdef', 'b.d');