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

Question

I have a sqlite table with following columns

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?

Michal Drozd · Accepted Answer · 2020-10-02 06:46:30Z

1

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

edited Oct 2, 2020 at 6:46

answered Oct 1, 2020 at 17:31

Michal Drozd

1,3515 gold badges13 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dont DownVote Please Over a year ago

Yes I can use this method but my query string list may contain more than 100 items. Is there any other efficient way?

Michal Drozd Over a year ago

talking about performance regexp is not better then OR operator. Even OR LIKE is much faster if you have INDEX on dates column and if you do prefix search LIKE 'dayOfMonth":3%'. If you use regexp then INDEX is not used.

GMB · Accepted Answer · 2020-10-01 17:30:29Z

1

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

answered Oct 1, 2020 at 17:30

GMB

224k25 gold badges103 silver badges151 bronze badges

1 Comment

Dont DownVote Please Over a year ago

REGEXP shows error. There is a problem with the query: [SQLITE_ERROR] SQL error or missing database (no such function: REGEXP)

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related