如何在Sqlite中使用查询字符串列表执行LIKE查询

huangapple go评论71阅读模式
英文:

How to perform LIKE query in Sqlite with a List of query String

问题

我有一个SQLite表,具有以下列
```sqlite
SELECT * FROM table_one
+------+----------+--------------------------------------------------------+
|  ID  |   name   |                         dates                          |
+------+----------+--------------------------------------------------------+
| 1001 | SINGLE   | [{"dayOfMonth":5,"month":9}]                           | 
| 1002 | MULTIPLE | [{"dayOfMonth":2,"month":9},{"dayOfMonth":3,"month":9} |
+------+----------+--------------------------------------------------------+

现在,如果我查询以下任一语句,将返回正确的信息:

SELECT * FROM table_one WHERE dates LIKE '%dayOfMonth":3%' 
SELECT * FROM table_one WHERE dates LIKE '%dayOfMonth":5%' 

但我想将这两个语句合并在一行中。我尝试过这个答案,但对我没用。

这里的日期是一个可变长度且不精确的变量。IN 操作也不起作用。
我想执行这个操作 List<Item> getAllOf(List<String> queryStrings),它将返回一个匹配的项目列表。有什么简单的方法吗?


<details>
<summary>英文:</summary>

I have a sqlite table with following columns
```sqlite
SELECT * FROM table_one
+------+----------+--------------------------------------------------------+
|  ID  |   name   |                         dates                          |
+------+----------+--------------------------------------------------------+
| 1001 | SINGLE   | [{&quot;dayOfMonth&quot;:5,&quot;month&quot;:9}]                           | 
| 1002 | MULTIPLE | [{&quot;dayOfMonth&quot;:2,&quot;month&quot;:9},{&quot;dayOfMonth&quot;:3,&quot;month&quot;:9} |
+------+----------+--------------------------------------------------------+

Now if I query either one of below return correct info:

SELECT * FROM table_one WHERE dates LIKE &#39;%dayOfMonth&quot;:3%&#39; 
SELECT * FROM table_one WHERE dates LIKE &#39;%dayOfMonth&quot;:5%&#39; 

But I want to combine this two statement in one single line. I've tried this answer but didn't worked for me.

Here dates is a variable length and not exact. The IN operation is also not working.
I want to perform this operation List&lt;Item&gt; getAllOf(List&lt;String&gt; queryStrings) which will return me a list of all matched item. What is the easiest way?

答案1

得分: 1

如果你要使用 LIKE 进行这样的操作,你需要重复条件,并使用 OR

SELECT * 
FROM table_one 
WHERE dates LIKE '%dayOfMonth":3%' OR dates LIKE '%dayOfMonth":5%'

一个正则表达式的解决方案如下:

SELECT * FROM table_one WHERE dates REGEXP 'dayOfMonth":[35]'

[35] 是一个字符类,意味着这两个字符中的任何一个都必须被找到。

如果你有多个字符,并且想要更通用的方法,可以使用 |(在正则表达式中基本上表示“或”):

SELECT * FROM table_one WHERE dates REGEXP 'dayOfMonth":(3)|(5)'

你可以在括号中放入任意多的字符。

英文:

If you are going to do this with LIKE, you need to repeat the conditions, using OR:

SELECT * 
FROM table_one 
WHERE dates LIKE &#39;%dayOfMonth&quot;:3%&#39; OR dates LIKE &#39;%dayOfMonth&quot;:5%&#39; 

A regex solution would look like:

SELECT * FROM table_one WHERE dates REGEXP &#39;dayOfMonth&quot;:[35]&#39; 

[35] is a character class, meaning that any of the two characters must be found.

A more generic approach if you have more than one character is to use | (which basically stands for "or" in regexes):

SELECT * FROM table_one WHERE dates REGEXP &#39;dayOfMonth&quot;:(3)|(5)&#39; 

You can put as many characters as you want within the parentheses

答案2

得分: 1

但我想将这两个语句合并成一行。

为什么你不在LIKE条件之间使用OR运算符呢? 如何在Sqlite中使用查询字符串列表执行LIKE查询

我的意思是

SELECT * FROM table_one WHERE dates LIKE '%dayOfMonth":3%' OR dates LIKE '%dayOfMonth":5%'

总的来说,在不必要的情况下不要在WHERE条件中使用REGEXP。使用正则表达式,您会失去索引搜索的优势。

英文:

> But I want to combine this two statement in one single line.

Why you just don't use OR operator between LIKE conditions ? 如何在Sqlite中使用查询字符串列表执行LIKE查询

I mean

SELECT * FROM table_one WHERE dates LIKE &#39;%dayOfMonth&quot;:3%&#39; OR dates LIKE &#39;%dayOfMonth&quot;:5%&#39; 

General rule here: don't use REGEXP in WHERE conditions if you don't have to. With regexp you lose INDEX search advantage..

huangapple
  • 本文由 发表于 2020年10月2日 01:26:30
  • 转载请务必保留本文链接:https://go.coder-hub.com/64160402.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定