How do I use regex in a SQLite query?

Question

I'd like to use a regular expression in sqlite, but I don't know how.

My table has got a column with strings like this: "3,12,13,14,19,28,32" Now if I type "where x LIKE '3'" I also get the rows which contain values like 13 or 32, but I'd like to get only the rows which have exactly the value 3 in that string.

Does anyone know how to solve this?

Sulamith

This answer is the best for adding REGEXP function to sqlite in c# https://mcmap.net/q/21925/-sqlite-net-sqlitefunction-not-working-in-linq-to-sql – Courcy

Answer 1

SQLite3 supports the REGEXP operator:

WHERE x REGEXP <regex>

http://www.sqlite.org/lang_expr.html#regexp

Cussedness

I found an easy way: It's simply \bx\b where x is the value to look for in the string :) – Sulamith

@DanS: How do you add a regex() function to support the REGEXP operator? By default a user function has not been added. – Hermy

According to the Sqlite docs: The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If a application-defined SQL function named "regexp" is added at run-time, that function will be called in order to implement the REGEXP operator. (sqlite.org/lang_expr.html#regexp) – Tempest

For those of us who get an error when you try this check out the response below https://mcmap.net/q/21924/-how-do-i-use-regex-in-a-sqlite-query I put the code in a file and imported it on the User-defined Functions in my Firefox sqlite manager. You need to invoke it slightly differently though, like this: SELECT * FROM table WHERE column regexp("myregexp") – Molly

@ugh! What is the point of adding REGEXP support if it doesn't work out of the box??! – Kincardine

@Kincardine Well I guess it's part of SQLite's philosophy, being light and customizable. It is intended to be embedded in applications, alongside any library you need. Plus it already has builtin pattern matching with the LIKE operator. Also I just tested REGEXP in my Linux distribution's SQLite and it works by default. – Precipitant

Answer 2

As others pointed out already, REGEXP calls a user defined function which must first be defined and loaded into the the database. Maybe some sqlite distributions or GUI tools include it by default, but my Ubuntu install did not. The solution was

sudo apt-get install sqlite3-pcre

which implements Perl regular expressions in a loadable module in /usr/lib/sqlite3/pcre.so

To be able to use it, you have to load it each time you open the database:

.load /usr/lib/sqlite3/pcre.so

Or you could put that line into your ~/.sqliterc.

Now you can query like this:

SELECT fld FROM tbl WHERE fld REGEXP '\b3\b';

If you want to query directly from the command-line, you can use the -cmd switch to load the library before your SQL:

sqlite3 "$filename" -cmd ".load /usr/lib/sqlite3/pcre.so" "SELECT fld FROM tbl WHERE fld REGEXP '\b3\b';"

If you are on Windows, I guess a similar .dll file should be available somewhere.

Sciomachy

Another load option: I created a view with this: SELECT load_extension('/usr/lib/sqlite3/pcre.so'); That way when I use a GUI based entry point to the DB (like SQLite Manager in Firefox), I have a way to load the REGEXP capability. – Bakker

SQLite version 3.36.0 released 2021-06-18 now has the REGEXP command builtin to the shell. – Loriloria

Answer 3

A hacky way to solve it without regex is where ',' || x || ',' like '%,3,%'

Doviedow

Yes I thought of that way, but there aren't leading or following "," everytime. Thanks anyway :-) – Sulamith

I didn't stumble upon the problem here - I wonder if this works as x is the column name... – Sulamith

You should use ',' || x || ',' – Emelia

Answer 4

SQLite does not contain regular expression functionality by default.

It defines a REGEXP operator, but this will fail with an error message unless you or your framework define a user function called regexp(). How you do this will depend on your platform.

If you have a regexp() function defined, you can match an arbitrary integer from a comma-separated list like so:

... WHERE your_column REGEXP "\b" || your_integer || "\b";

But really, it looks like you would find things a whole lot easier if you normalised your database structure by replacing those groups within a single column with a separate row for each number in the comma-separated list. Then you could not only use the = operator instead of a regular expression, but also use more powerful relational tools like joins that SQL provides for you.

Wurster

Answer 5

A SQLite UDF in PHP/PDO for the REGEXP keyword that mimics the behavior in MySQL:

$pdo->sqliteCreateFunction('regexp',
    function ($pattern, $data, $delimiter = '~', $modifiers = 'isuS')
    {
        if (isset($pattern, $data) === true)
        {
            return (preg_match(sprintf('%1$s%2$s%1$s%3$s', $delimiter, $pattern, $modifiers), $data) > 0);
        }

        return null;
    }
);

The u modifier is not implemented in MySQL, but I find it useful to have it by default. Examples:

SELECT * FROM "table" WHERE "name" REGEXP 'sql(ite)*';
SELECT * FROM "table" WHERE regexp('sql(ite)*', "name", '#', 's');

If either $data or $pattern is NULL, the result is NULL - just like in MySQL.

Methionine

Answer 6

My solution in Python with sqlite3:

import sqlite3
import re

def match(expr, item):
    return re.match(expr, item) is not None

conn = sqlite3.connect(':memory:')
conn.create_function("MATCHES", 2, match)
cursor = conn.cursor()
cursor.execute("SELECT MATCHES('^b', 'busy');")
print cursor.fetchone()[0]

cursor.close()
conn.close()

If regex matches, the output would be 1, otherwise 0.

Eugenle

Answer 7

With python, assuming con is the connection to SQLite, you can define the required UDF by writing:

con.create_function('regexp', 2, lambda x, y: 1 if re.search(x,y) else 0)

Here is a more complete example:

import re
import sqlite3

with sqlite3.connect(":memory:") as con:
    con.create_function('regexp', 2, lambda x, y: 1 if re.search(x,y) else 0)
    cursor = con.cursor()
    # ...
    cursor.execute("SELECT * from person WHERE surname REGEXP '^A' ")

Oldtime

IMHO check should be if x not Null and y not Null and re.search(x,y) otherwise it will throw. – Fury

Answer 8

For those who think that SQLite itself provide the function REGEXP, one basic requirement to invoke the function REGEXP in SQLite is: "You should create your own function in the application and then provide the callback link to the sqlite driver".
For that, you have to use sqlite_create_function (C interface). You can find the detail from here and here

Snowblind

Answer 9

An exhaustive or'ed where clause can do it without string concatenation:

WHERE ( x == '3' OR
        x LIKE '%,3' OR
        x LIKE '3,%' OR
        x LIKE '%,3,%');

Includes the four cases exact match, end of list, beginning of list, and mid list.

This is more verbose, doesn't require the regex extension.

Rent

Answer 10

Consider using this

WHERE x REGEXP '(^|,)(3)(,|$)'

This will match exactly 3 when x is in:

• 3
• 3,12,13
• 12,13,3
• 12,3,13

Other examples:

WHERE x REGEXP '(^|,)(3|13)(,|$)'

This will match on 3 or 13

Sukkah

Answer 11

UPDATE TableName
 SET YourField = ''
WHERE YourField REGEXP 'YOUR REGEX'

And :

SELECT * from TableName
 WHERE YourField REGEXP 'YOUR REGEX'

Blueing

Answer 12

SQLite version 3.36.0 released 2021-06-18 now has the REGEXP command builtin. For CLI build only.

Eggett

Ref: sqlite.org/releaselog/3_36_0.html – Cogent

3.38.0: "no such function: REGEXP" – Kincardine

That is only for the CLI builds. – Poorhouse

It works! I here on Debian I can simply use WHERE POINTID REGEXP "CO060240S0630W0.1..1.." instead of just WHERE POINTID LIKE "CO060240S0630W0_1__1__"! – Magpie

Answer 13

You may consider also

WHERE x REGEXP '(^|\D{1})3(\D{1}|$)'

This will allow find number 3 in any string at any position

Unsteady

Answer 14

You can use the sqlean-regexp extension, which provides regexp search and replace functions.

Based on the PCRE2 engine, this extension supports all major regular expression features. It also supports Unicode. The extension is available for Windows, Linux, and macOS.

Some usage examples:

-- select messages containing number 3
select * from messages
where msg_text regexp '\b3\b';

-- count messages containing digits
select count(*) from messages
where msg_text regexp '\d+';
-- 42

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

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

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

Rim

Answer 15

You could use a regular expression with REGEXP, but that is a silly way to do an exact match.

You should just say WHERE x = '3'.

Pembrook

I should have explained it better (sorry for my poor english), I meant only a certain exact value, not the exact string. Thanks anyway! – Sulamith

Answer 16

If you are using php you can add any function to your sql statement by using: SQLite3::createFunction. In PDO you can use PDO::sqliteCreateFunction and implement the preg_match function within your statement:

See how its done by Havalite (RegExp in SqLite using Php)

Gelatin

In MySQL REGEXP function you don't need to specify the delimiters or modifiers in the pattern. – Methionine

Answer 17

In case if someone looking non-regex condition for Android Sqlite, like this string [1,2,3,4,5] then don't forget to add bracket([]) same for other special characters like parenthesis({}) in @phyatt condition

WHERE ( x == '[3]' OR
        x LIKE '%,3]' OR
        x LIKE '[3,%' OR
        x LIKE '%,3,%');

Peptidase

Answer 18

In Julia, the model to follow can be illustrated as follows:

using SQLite
using DataFrames

db = SQLite.DB("<name>.db")

register(db, SQLite.regexp, nargs=2, name="regexp")

SQLite.Query(db, "SELECT * FROM test WHERE name REGEXP '^h';") |> DataFrame

Oldtime

Answer 19

for rails

            db = ActiveRecord::Base.connection.raw_connection
            db.create_function('regexp', 2) do |func, pattern, expression|
              func.result = expression.to_s.match(Regexp.new(pattern.to_s, Regexp::IGNORECASE)) ? 1 : 0
            end

Theravada

Answer 20

There's an SQLite extension called sqlite_jq (https://github.com/mgdm/sqlite-jq) which allows one to use jq's regexp functions, which are more powerful that SQLite's as suggested by the second of the following examples.

Using the table created by test_table.sql (https://github.com/mgdm/sqlite-jq/blob/main/test_table.sql)

sqlite> select jq(raw,'.[]|select(.id|test("6$") ).id' ) from test;
24583836616

sqlite> select jq(raw,'.[] | .actor.avatar_url | capture("https://(?<domain>[^.]*)")' ) from test;
[{"domain":"avatars"},{"domain":"avatars"}]

The extension also provides a jq_each() table-valued function.

---

The neat thing is that the jq() function is marked as "deterministic" and can therefore be used in "CREATE INDEX" commands.

Oldtime

Answer 21

The easiest way I found to install a REGEXP extension is with sqlpkg-cli.

Works on Linux, MacOS and Windows.

The (unofficial) SQLite package manager

sqlpkg manages SQLite extensions, just like pip does with Python packages or brew does with macOS programs.

It works primarily with the SQLite package registry, but is not limited to it. You can install SQLite extensions from GitHub repositories or other websites. All you need is a package spec file...

Install the cli:

curl -sS https://beta.webi.sh/sqlpkg | sh

Install the extension:

sqlpkg install nalgeon/regexp

Load the extension (MacOS, NodeJS):

import Database from "better-sqlite3";
const db = new Database('path/to/db');
try {
  db.loadExtension('/Users/<username>/.sqlpkg/nalgeon/regexp/regexp.dylib');
} catch (err) {
  console.error('Failed to load extension:', err);
}

Run a REGEX query (NodeJS example):

const query = `SELECT * from table
               WHERE column REGEXP 'include|these|strings'
               AND column NOT REGEXP 'but|not|others'`;

const query = db.prepare(queryString);

const results = query.all();

Kb