如何在SQLite查询中使用regex ?

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

我想在sqlite中使用正则表达式，但是我不知道如何使用。

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.

我的表有一列有这样的字符串:"3,12,13,14,19,28,32"现在如果我输入"where x like '3 "我也会得到包含值为13或32的行，但我只希望得到值为3的行。

Does anyone know how to solve this?

有人知道怎么解决这个问题吗?

12 个解决方案

#1

SQLite3 supports the REGEXP operator:

SQLite3支持REGEXP操作符:

WHERE x REGEXP <regex>

#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

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

sudo apt-get install sqlite3-pcre

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

它在/usr/lib/sqlite3/ pcreis中的可加载模块中实现Perl正则表达式

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.

或者你可以把这一行写进~/.sqliterc。

Now you can query like this:

现在你可以这样查询:

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

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

如果你在Windows上，我猜一个类似的。dll文件应该可以在其他地方找到。

#3

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

不使用regex解决它的一种常见方法是，' || x || '，' like '%，3，%'

#4

SQLite does not contain regular expression functionality by default.

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

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.

它定义了一个REGEXP操作符，但如果没有您或您的框架定义一个名为REGEXP()的用户函数，则会出现错误消息。如何做到这一点将取决于您的平台。

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

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

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

但实际上，如果您将数据库结构规范化，在一个列中替换这些组，并为逗号分隔的列表中的每个数字使用单独的行，那么看起来您会发现事情要简单得多。然后，您不仅可以使用=运算符而不是正则表达式，还可以使用更强大的关系工具，如SQL为您提供的连接。

#5

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

PHP/PDO中的SQLite UDF，用于模仿MySQL中的行为的REGEXP关键字:

$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:

u修饰符没有在MySQL中实现，但是我发现默认使用它很有用。例子:

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.

如果$data或$pattern都为NULL，则结果为NULL——就像在MySQL中那样。

#6

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.

我不认为回答一个差不多一年前发布的问题是件好事。但我为那些认为Sqlite本身提供了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

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

#7

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

And :

和:

SELECT * from TableName
 WHERE YourField REGEXP 'YOUR REGEX'

#8

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

您可以使用正则表达式与REGEXP一起使用，但这是一种做精确匹配的愚蠢方法。

You should just say WHERE x = '3'.

你应该说x = '3'

#9

Consider using this

考虑使用这

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

This will match exactly 3 when x is in:

当x在:

3
3
3,12,13
3、12、13所示
12,13,3
12日,13日,3
12,3,13
12、3、13

Other examples:

其他的例子:

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

This will match on 3 or 13

这将匹配3或13

#10

my solution in python with sqlite3:

我在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()

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

如果regex匹配，输出将为1，否则为0。

#11

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:

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

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

看看Havalite(使用Php在SqLite中使用RegExp)是如何实现的

#12

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

一个详尽的或ed where子句可以在没有字符串连接的情况下完成:

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.

这更冗长，不需要regex扩展。

#1