如何在SQLite查询中使用正则表达式？

Question

我想在sqlite中使用正则表达式，但是不知道怎么做。

我的表格有一个列，其中包含像这样的字符串：“3,12,13,14,19,28,32”。 现在，如果我输入“where x LIKE '3'”，我也会得到包含值13或32之类的行， 但是我只想得到具有该字符串中确切值3的行。

有人知道如何解决这个问题吗？

- cody

---

这个答案是在C#中为SQLite添加REGEXP函数的最佳方法 https://dev59.com/d2Af5IYBdhLWcg3w0Vi7#26155359 - hubaishan

Answer 1

正如其他人已经指出的那样，REGEXP调用了一个用户定义的函数，必须先定义并加载到数据库中。也许某些SQLite发行版或GUI工具默认包含它，但我的Ubuntu安装没有包含它。解决方案是

sudo apt-get install sqlite3-pcre

该模块在/usr/lib/sqlite3/pcre.so中实现了Perl正则表达式。

为了能够使用它，每次打开数据库时都需要加载它：

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

或者，你可以将那行代码放入你的~/.sqliterc文件中。

现在你可以这样查询：

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

如果你想直接从命令行查询，你可以使用-cmd开关在执行 SQL 前加载库：

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

如果你使用的是Windows操作系统，我猜想类似的.dll文件应该可以在某个地方找到。

- mivk

---

另一个加载选项：我使用以下语句创建了一个视图：SELECT load_extension('/usr/lib/sqlite3/pcre.so'); 这样，当我使用基于GUI的数据库入口（例如Firefox中的SQLite Manager）时，就可以加载REGEXP功能了。 - Paulb

SQLite 3.36.0 版本已于2021-06-18发布，现在Shell中内置了“REGEXP”命令。 - user1461607

Answer 2

SQLite3 支持 REGEXP 运算符：

WHERE x REGEXP <regex>

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

- DanS

---

我发现了一个简单的方法：只需在字符串中使用\bx\b，其中x是要查找的值 :) - cody

你想要添加一个regex()函数来支持REGEXP运算符，该怎么做？默认情况下尚未添加用户函数。 - Ken

根据Sqlite文档：REGEXP运算符是regexp()用户函数的特殊语法。默认情况下不定义regexp()用户函数，因此使用REGEXP运算符通常会导致错误消息。如果运行时添加名为"regexp"的应用程序定义的SQL函数，则调用该函数以实现REGEXP运算符。(http://www.sqlite.org/lang_expr.html#regexp) - radicand

对于那些在尝试时遇到错误的人，请查看以下响应：https://dev59.com/FW445IYBdhLWcg3wBl6J#18484596。我将代码放在文件中，并在Firefox sqlite管理器中导入了它。然而，你需要稍微不同地调用它，就像这样：SELECT * FROM table WHERE column regexp("myregexp")。 - Tristan

@ 呜！如果它不能开箱即用，那么添加 REGEXP 支持有什么意义？？！ - Michael

@Michael 我想这是SQLite哲学的一部分，它旨在轻量和可定制。它旨在嵌入应用程序，与您需要的任何库一起使用。此外，它已经具有内置的LIKE操作符模式匹配功能。另外，我刚刚在我的Linux发行版的SQLite中测试了REGEXP，它默认就可以使用。 - renardesque

Answer 3

一种不使用正则表达式解决此问题的笨办法是：where ',' || x || ',' like '%,3,%'

- Blorgbeard

---

是的，我也考虑过这种方式，但并不是每次都有前导或后继逗号。无论如何还是谢谢您 :-) - cody

我在这里没有遇到问题-我想知道这是否作为x列名称有效... - cody

您应该使用 ',' || x || ','。此语句意为在变量x前后加上逗号。 - Baruch

Answer 4

SQLite默认不包含正则表达式功能。

它定义了一个REGEXP运算符，但是如果您或您的框架没有定义一个名为regexp()的用户函数，则会失败并显示错误消息。如何执行此操作取决于您的平台。

如果您已经定义了regexp()函数，则可以像这样匹配逗号分隔列表中的任意整数：

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

但实际上，如果您通过用单独的行替换逗号分隔列表中的组在单个列中，使数据库结构标准化, 您会发现事情变得更加容易。然后，您不仅可以使用=运算符而不是正则表达式，还可以使用SQL提供的更强大的关系工具，如连接。

- Ian Mackinnon

Answer 5

一个在PHP/PDO中实现了REGEXP关键字，模拟MySQL行为的SQLite UDF：

$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;
    }
);

u修饰符在MySQL中未实现，但我发现将其默认设置会很有用。例如：

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

如果$data或$pattern之一为NULL，则结果为NULL - 就像在MySQL中一样。

- Alix Axel

Answer 6

我在Python中使用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()

如果正则表达式匹配成功，输出将为1，否则为0。

- aGuegu

Answer 7

使用Python，假设con是SQLite的连接，你可以通过编写以下代码来定义所需的UDF：

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

这是一个更完整的例子：

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

- peak

---

在我看来，检查应该是 if x 不为 Null and y 不为 Null and re.search(x,y)，否则它会抛出异常。 - pholat

Answer 8

使用完整的或运算 where 子句可以避免使用字符串连接：

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

包括四种情况：精确匹配、列表末尾、列表开头和列表中间。

这种方法更加详细，不需要使用正则表达式扩展。

- phyatt

Answer 9

虽然这个问题发布已经快一年了，但我认为回答它对于那些认为Sqlite本身提供REGEXP函数的人来说仍是有用的。

使用Sqlite中的函数REGEXP的一个基本要求是：
“你应该在应用程序中创建自己的函数，然后将回调链接提供给sqlite驱动程序”。
为此，您需要使用sqlite_create_function（C接口）。您可以从此处和此处找到详细信息。

- Naved

Answer 10

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

并且：

SELECT * from TableName
 WHERE YourField REGEXP 'YOUR REGEX'

- user4813855