0

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?

Dont DownVote Please
  • 21
  • 6

2 Answers2

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

GMB
  • 216,147
  • 25
  • 84
  • 135
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..

Michal Drozd
  • 1,311
  • 5
  • 13
  • 26
  • 2
    Yes I can use this method but my query string list may contain more than 100 items. Is there any other efficient way? – Dont DownVote Please Oct 02 '20 at 03:52
  • 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. – Michal Drozd Oct 02 '20 at 06:43