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

小开

您可以在 REGEXP中使用正则表达式，但是这种做完全匹配的方法很愚蠢。

你应该说 WHERE x = '3'。

小开

最佳答案

SQLite3支持 REGEXP 操作符:

WHERE x REGEXP <regex>

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

小开

不使用正则表达式解决这个问题的一种常用方法是 where ',' || x || ',' like '%,3,%'

小开

考虑用这个

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

当 x 为:

3
三，十二，十三
十二，十三，三
12313

其他例子:

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

这个会在3或13上匹配

小开

回答一个将近一年前发布的问题我觉得不太好。但是我写这篇文章是为了那些认为 Sqlite 本身提供了 REGEXP函数的人。

在 sqlite 中调用函数 REGEXP 的一个基本要求是
“您应该在应用程序中创建自己的函数，然后提供 sqlite 驱动程序的回调链接” 。
你必须使用 sqlite _ create _ function (C 接口) ，你可以在给你和这里找到详细信息

小开

默认情况下，SQLite 不包含正则表达式功能。

它定义了一个 REGEXP操作符，但是如果您或者您的框架定义用户函数不能调用 regexp()，那么这个操作将会失败并出现错误消息。如何做到这一点将取决于您的平台。

如果定义了一个 regexp()函数，那么可以从逗号分隔的列表中匹配任意整数，如下所示:

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

但实际上，如果您将标准化了你的数据库结构替换为以逗号分隔的列表中的每个数字的单独行，那么您会发现事情变得容易得多。然后，您不仅可以使用 =运算符而不是正则表达式，而且还可以使用 SQL 为您提供的联接等更强大的关系工具。

小开

正如其他人已经指出的那样，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 文件应该可以在某个地方找到。

小开

如果使用 php，可以使用 SQLite3: : createFunction向 sql 语句添加任何函数。在 PDO 中，您可以使用 SqliteCreatefunction并在语句中实现 Preg _ match函数:

看看它是如何做的哈瓦利(在 SqLite 中使用 PHP 实现 RegExp)

小开

PHP/PDO 中用于模仿 MySQL 中行为的 REGEXP关键字的 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 中没有实现，但是我发现默认情况下使用它是很有用的:

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

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

小开

我在 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。

小开

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

还有:

SELECT * from TableName
WHERE YourField REGEXP 'YOUR REGEX'

小开

一个穷举的或者’ed where 子句不需要字符串连接就可以做到这一点:

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

包括四种情况下完全匹配，结束列表，开始列表，中期列表。

这更加冗长，不需要正则表达式扩展。

小开

你也可以考虑

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

这将允许在任何位置的任何字符串中查找数字3

小开

如果有人为 Android Sqlite查找非正则表达式条件，比如这个字符串 [1,2,3,4,5]，那么不要忘记在@phyatt 条件中为其他特殊字符(如括号({}))添加括号([])

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

小开

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

小开

在《茱莉亚》中，应遵循的模式可以说明如下:

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

小开

铁路

            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

小开

发布于2021-06-18的 SQLite 版本3.36.0现在内置了 REGEXP 命令。仅用于 CLI 构建。