regexp.md

regexp: Regular expressions in SQLite

The sqlean-regexp extension provides regexp search and replace functions. Supports all major regular expression features (see the section on syntax below).

Supports Unicode in character classes (like \w) and assertions (like \b).

Reference • Supported syntax • Acknowledgements • Installation and usage

Reference

regexp • like • substr • capture • replace

REGEXP statement

Checks if the source string matches the pattern.

select true where 'the year is 2021' regexp '[0-9]+';
-- 1

regexp_like

regexp_like(source, pattern)

Checks if the source string matches the pattern.

select regexp_like('the year is 2021', '[0-9]+');
-- 1
select regexp_like('the year is 2021', '2k21');
-- 0

regexp_substr

regexp_substr(source, pattern)

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

select regexp_substr('the year is 2021', '[0-9]+');
-- 2021
select regexp_substr('the year is 2021', '2k21');
-- (null)

regexp_capture

regexp_capture(source, pattern [, n])

Finds a substring of the source string that matches the pattern and returns the nth matching group within that substring. Group numbering starts at 1. n = 0 (default) returns the entire substring.

select regexp_capture('years is 2021', '\d\d(\d\d)', 0);
-- 2021
select regexp_capture('years is 2021', '\d\d(\d\d)', 1);
-- 21

regexp_replace

regexp_replace(source, pattern, replacement)

Replaces all matching substrings with the replacement string.

select regexp_replace('the year is 2021', '[0-9]+', '2050');
-- the year is 2050
select regexp_replace('the year is 2021', '2k21', '2050');
-- the year is 2021

Supports backreferences to captured groups $1 trough $9 in the replacement string:

select regexp_replace('the year is 2021', '([0-9]+)', '$1 or 2050');
-- the year is 2021 or 2050

Supported syntax

Basic expressions:

.        any character except newline
a        the character a
ab       the string ab
a|b      a or b
\        escapes a special character

Quantifiers:

*        0 or more
+        1 or more
?        0 or 1
{n}      exactly n
{n,m}    between n and m
{n,}     n or more

Quantifiers are greedy by default (i.e., match as much text as possible). Add ? to make them lazy (i.e., match as little text as possible):

*?       0 or more (lazy)
+?       1 or more (lazy)

Groups:

(...)    capturing group
(?:...)  non-capturing group
(?>...)  atomic group
\N       match the Nth captured group

Character classes:

[ab-d]   one character of: a, b, c, d
[^ab-d]  one character except: a, b, c, d
\d       one digit
\D       one non-digit
\s       one whitespace
\S       one non-whitespace
\w       one word character
\W       one non-word character

Assertions:

^        start of string
$        end of string
\b       word boundary
\B       non-word boundary
(?=...)  positive lookahead
(?!...)  negative lookahead

Options:

(?i)    case-insensitive matching

select regexp_substr('the YEAR is 2021', 'year');
-- (null)
select regexp_substr('the YEAR is 2021', '(?i)year');
-- YEAR

Acknowledgements

Based on the PCRE2 engine.

Installation and usage

SQLite command-line interface:

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

See How to install an extension for usage with IDE, Python, etc.

⬇️ Download • ✨ Explore • 🚀 Follow