Query using LIKE isn't working using GreenDAO

Question

I have a table which a column is a string with three characters, each character has a value 0 or 1. I'd like to select those rows according to the case.

I'd like to perform a query like that:

SELECT * FROM Item WHERE group_type LIKE ?

? can be 100 or 101 or 011 or 111 or 001. A combination with 0 and 1 in three characters.

I'm trying to query using LIKE

WhereCondition where = null;
switch (condition) {
    case case1:
        where = ItemDao.Properties.GroupType.like("1%");
        break;
    case case2:
        where = ItemDao.Properties.GroupType.like("%1%");
        break;
    case case3:
        where = ItemDao.Properties.GroupType.like("%1");
        break;
}
List<Item> items = itemDao.queryBuilder().where(where).list();

case1 is returning everything that starts with 1 as expected. case3 is returning everything that ends with 1 as expected. case2 is returning everything! It doesn't metter the value in the beggining, middle or end. It's returning everything.

case1 and case3 are working fine. However, case2 isn't working. Is there any problem with that?

You must elaborate more on how exactly the case2 doesn't work? What is the output..? — Vucko, May 28 '16 at 15:58
Well that's to be expected, each of these numbers contains a 1. It does not mean it **must** have something before and after it, %1% means that it can, but doesn't have to have something before and after. — Vucko, May 28 '16 at 17:25
Well I have thought of a hacky way to do it, to post-filter the data ( I don't think the query can be much improved ). Get all the data and the parse the string to see if it contains the '1', and if it does, does it have something before and after it. I'm quite sorry, but that's all I got. — Vucko, May 28 '16 at 17:40
Yeah I thought about it. However I think to query direcly is better. I also tought about create 3 columns which means each case... — Douglas Fornaro, May 28 '16 at 18:02
It is still not completely clear what you expect for case2, but if you want your like clause to match every 3 char string with '1' in second position, you can use the underscore wildcard (`_`) for first and third char, i.e., `like("_1_")`. — gcressier, May 31 '16 at 17:38
@gcressier I didn't know about wildcard, it will solve the problem! Thanks. — Douglas Fornaro, Jun 03 '16 at 22:07

score 0 · Answer 1 · answered Jan 04 '18 at 22:41

0

No. "%1%" is supposed to return everything where "1" occurs. It can be "100", "010" or "101", but not "000".

answered Jan 04 '18 at 22:41

Ana Gurgenidze

1

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

From the sqlite like clause reference page:

The percent sign represents zero, one, or multiple numbers or characters. The underscore represents a single number or character. These symbols can be used in combinations.

By saying %1% you are searching for elements that have at least one number 1, regardless where it may be. This is mostly because % meaning zero, one or multiple occurrences. This is further explained in that link by the following example:

WHERE SALARY LIKE '%200%'

Finds any values that have 200 in any position

So we can see that indeed you are getting the expected behavior.

Query using LIKE isn't working using GreenDAO

2 Answers2