英文:
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 | [{"dayOfMonth":5,"month":9}] |
| 1002 | MULTIPLE | [{"dayOfMonth":2,"month":9},{"dayOfMonth":3,"month":9} |
+------+----------+--------------------------------------------------------+
Now if I query either one of below return correct info:
SELECT * FROM table_one WHERE dates LIKE '%dayOfMonth":3%'
SELECT * FROM table_one WHERE dates LIKE '%dayOfMonth":5%'
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<Item> getAllOf(List<String> 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 '%dayOfMonth":3%' OR dates LIKE '%dayOfMonth":5%'
A regex solution would look like:
SELECT * FROM table_one WHERE dates REGEXP 'dayOfMonth":[35]'
[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 'dayOfMonth":(3)|(5)'
You can put as many characters as you want within the parentheses
答案2
得分: 1
但我想将这两个语句合并成一行。
为什么你不在LIKE条件之间使用OR运算符呢?
我的意思是
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 ?
I mean
SELECT * FROM table_one WHERE dates LIKE '%dayOfMonth":3%' OR dates LIKE '%dayOfMonth":5%'
General rule here: don't use REGEXP in WHERE conditions if you don't have to. With regexp you lose INDEX search advantage..
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论