如何在 Sqlite Android 中实现单词边界? [英] How to achieve word boundary in Sqlite Android?

问题描述

我想实现以下(全文搜索)，

I would like to achieve the following (a full text search),

SELECT * FROM tablename where columnname REGEXP '[[:<:]]some string[[:>:]]'

我只对全文列中的确切字符串(而不仅仅是单词)感兴趣.

Where i am interested in only exact strings (not just words) from a full text column.

我一直在 MySQL 中使用上述 SQL，现在将大部分代码迁移到 Android 应用程序.

I have been using the exact SQL above in MySQL and now migrating most of the code to android apps.

但我一直在查看各种帖子，其中提到 Android Sqlite 不支持 REGEXP(例如:link1, link2，link3).

But I have been looking at various posts where it is mentioned that REGEXP isn't supported in Android Sqlite (for example: link1, link2, link3 ).

有没有办法在 Android 中启用 REGEXP?

Is there a way to enable REGEXP in Android?

如果没有，上面的 SQL 有什么替代方案吗?

谢谢，

目前我在 Android 中使用 REGEXP 时收到以下异常，

Currently I am receiving the following exception when using REGEXP in Android,

android.database.sqlite.SQLiteException: no such function: REGEXP (code 1):...

我知道我们可以使用 GLOB 和 LIKE (甚至可能是 MATCH ).如何将 columnname REGEXP '[[:<:]]somestring[[:>:]]' 转换为使用 GLOB 和/或 LIKE 和/或 MATCH?

I understand we can make use of GLOB and LIKE ( or may be even MATCH ). How can columnname REGEXP '[[:<:]]somestring[[:>:]]' be converted to make use of GLOB and/or LIKE and/or MATCH?

解决方案 1:在 @cybersam 的帮助下，我正在使用以下内容(经过一些修改)

SOLUTION 1: With @cybersam's help following is what I am using (after some modifications)

SELECT * FROM tablename where
  (columnname GLOB '*some string*' OR columnname GLOB '*Some string*') AND 
(
(
    (columnname GLOB '*[^a-zA-Z0-9_]some string[^a-zA-Z0-9_]*' AND
    columnname GLOB '*some string[^a-zA-Z0-9_]*' AND
    columnname GLOB '*[^a-zA-Z0-9_]some string*')
OR
    (columnname GLOB '*[^a-zA-Z0-9_]Some string[^a-zA-Z0-9_]*' AND
    columnname GLOB '*Some string[^a-zA-Z0-9_]*' AND
    columnname GLOB '*[^a-zA-Z0-9_]Some string*')
)
)

GLOB 区分大小写，所以我有一个额外的 OR

GLOB is case-sensitive so I have an additional OR

@cybersam 的第二种解决方案在我的情况下要快得多.

@cybersam's second solution is much faster in my case.

解决方案 2:处理不区分大小写的问题

SOLUTION 2: To handle case in-sensitivity

SELECT * FROM tablename where
  (columnname GLOB '*[sS][oO][mM][eE] [sS][tT][rR][iI][nN][gG]*') AND 
(
    (
    columnname GLOB '*[^a-zA-Z0-9_][sS][oO][mM][eE] [sS][tT][rR][iI][nN][gG][^a-zA-Z0-9_]*' AND 
    columnname GLOB '*[sS][oO][mM][eE] [sS][tT][rR][iI][nN][gG][^a-zA-Z0-9_]*' AND 
    columnname GLOB '*[^a-zA-Z0-9_][sS][oO][mM][eE] [sS][tT][rR][iI][nN][gG]*')
)

推荐答案

要真正支持 REGEXP，您必须添加自己的 regexp() 用户函数.

To actually support REGEXP, you will have to add your own regexp() user function.

此链接可能对您有所帮助弄清楚通常如何为 Android 创建用户定义的函数——但这并不简单.

This link might help you figure out how to create user defined functions, in general, for Android -- but it is not simple.

如果您的模式非常简单，GLOB 运算符可能就足够了.

If your patterns are very simple, the GLOB operator might be good enough.

例如，要执行与此 MYSQL 查询等效的搜索:

For example, to perform a search equivalent to this MYSQL query:

SELECT * FROM tablename where columnname REGEXP '[[:<:]]some string[[:>:]]'

你可以在 SQLite 中试试这个:

you can try this in SQLite:

SELECT * FROM tablename where
  columnname GLOB '[^a-zA-Z0-9_]some string[^a-zA-Z0-9_]' OR
  columnname GLOB 'some string[^a-zA-Z0-9_]' OR
  columnname GLOB '[^a-zA-Z0-9_]some string' OR
  columnname GLOB 'some string';

上述查询使用的事实是，在 MYSQL 中，单词字符被定义为字母数字字符或下划线.还需要额外的 OR 项来匹配 some string 的任一侧(或两侧)都没有文本的情况.

The above query uses the fact that in MYSQL, a word character is defined to be either an alphanumeric character or an underscore. The additional OR terms are needed to also match the cases where there is no text on either (or both) sides of some string.

最后，如果 columnname 中的 'some string' 相对较少，那么下面更长的查询实际上可能会更快，因为它只会对一个GLOB 求值少数值:

Finally, if 'some string' is relatively rare in columnname, then the following longer query may actually be faster, since it will only do multiple GLOB evaluations for a minority of values:

SELECT * FROM tablename where
  columnname GLOB '*some string*' AND (
    columnname GLOB '[^a-zA-Z0-9_]some string[^a-zA-Z0-9_]' OR
    columnname GLOB 'some string[^a-zA-Z0-9_]' OR
    columnname GLOB '[^a-zA-Z0-9_]some string' OR
    columnname GLOB 'some string');

这篇关于如何在 Sqlite Android 中实现单词边界?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！