How do I use regex in a SQLite query?

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?

18 Answers

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.

SQLite3 supports the REGEXP operator:

WHERE x REGEXP <regex> 

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

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.

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.

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.

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' ") 

I don't it is good to answer a question which was posted almost an year ago. But I am writing this 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

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.

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

And :

SELECT * from TableName WHERE YourField REGEXP 'YOUR REGEX' 

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

You may consider also

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

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

SQLite version 3.36.0 released 2021-06-18 now has the REGEXP command builtin.

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'.

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,%'); 

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)

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 

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