How to use regular expressions (regex) in SQLite

SQLite is a lightweight, serverless database engine that's especially popular for small to medium-sized applications. One of the hurdles you might face when working with SQL databases is manipulating and querying text data. Regular expressions, commonly known as regex, offer a powerful way to conduct such tasks. While smaller databases like SQLite don't support regex natively, there are ways to use them with the help of extensions and user-defined functions.

Enabling Regular Expressions in SQLite
1. Using REGEXP in SQLite
  1. Step 1: Set Up SQLite with Regex Support
2. Regex Basics
Using SQLite outside of Python

Enabling Regular Expressions in SQLite

SQLite, by default, does not support regular expressions in its syntax. To use regex, you can compile SQLite with extensions or use the built-in Go SQLite wrapper (if you're using Go) that provides regex functionalities. Alternatively, you could write your own function to expose regular expression support.

Using REGEXP in SQLite

To utilize regular expressions in SQLite, you can follow these steps:

Step 1: Set Up SQLite with Regex Support

If you're using a prebuilt SQLite binary, you might want to enable regex through an extension or through a SQLite API in your programming language of choice.

For example, in Python, using the sqlite3 module, you can achieve this by defining a user function, which allows you to introduce regex match capabilities.

import sqlite3
import re

# Connect to SQLite database
db = sqlite3.connect(':memory:')

# Define a custom regex function
def regexp(pattern, item):
    return re.search(pattern, item) is not None

# Register the regexp function with SQLite
db.create_function("REGEXP", 2, regexp)

# Create a sample table and conduct regex queries
cursor = db.cursor()
cursor.execute("CREATE TABLE sample(text_col TEXT)")
rows = [('Hello World'), ('This is regex'), ('SQLite with Regex')]
cursor.executemany("INSERT INTO sample VALUES (?)", rows)

# Query using regex pattern
sql = "SELECT * FROM sample WHERE text_col REGEXP 'regex'")
for row in cursor.execute(sql):
    print(row)

This code snippet demonstrates using a custom defined REGEXP function. It helps in querying records whose text meets the given regular expression.

Regex Basics

When using regex with SQLite, understanding basic regex patterns and operations is crucial. Here are some:

Dot (.) - Matches any single character except newline.
Asterisk (*) - Matches 0 or more occurrences of the preceding element.
Plus (+) - Matches 1 or more occurrences of the preceding element.
Question Mark (?) - Matches 0 or 1 occurrence of the preceding element.
Square Brackets ([]) - Used to specify a set of characters to match.

Consider trying more complex examples to exploit regex capabilities when searching within SQLite tables:

SELECT * FROM sample WHERE text_col REGEXP '[a-z]+\s(World|regex)';

In this example, the query still checks for words followed by either 'World' or 'regex' within a string.

Using SQLite outside of Python

If you're not comfortable with Python or prefer a different language, similar principles apply:

Using JavaScript and sqlite3 in Node.js

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:');

// Custom REGEXP function
function createRegExpFunction(db) {
  db.run("SELECT load_extension('REGEXP')");
}

db.serialize(() => {
  db.run('CREATE TABLE sample (text_col TEXT)');
  db.run("INSERT INTO sample VALUES (?),(?),(?)",
    ['Hello World', 'Javascript and regex', 'Another example']
  );

  db.each("SELECT * FROM sample WHERE text_col REGEXP 'regex'", (err, row) => {
    if (err) throw err;
    console.log(row);
  });
});

Here, SQLite is set up within a Node.js environment, using the same method to introduce the regex function which allows text search by patterns.

In conclusion, even though SQLite doesn't natively support regex, wrapping it around with languages that do, like Python or JavaScript using Node.js, provides significant flexibility and capability. The custom setup enables powerful text parsing directly within SQL execution, enhancing the database functions you perform with SQLite tremendously.

Previous Article: SQLite UPSERT tutorial (insert if not exist, update if exist)

Series: CRUD Operations in SQLite

SQLite