SQLite query with [^] not working

Question

I checked the many posts on SO and they seem to match what I'm doing, but I can't get it to work, so maybe I'm missing something obvious. Like many others, I'm trying to match only words and not partial matches, I tried all the [^a-zA-Z] and nothing matches, so I tried to narrow it down to a simpler testcase, it seems like the [ ] don't work at all for me. I'm using this code:

String flashcardSearchQuery = "SELECT * FROM " + TABLE_NAME + " where " + COLUMN_ENGLISH_WORD +
        " LIKE " + "'%" + search_str + "[^,]%'";

ArrayList<Flashcard> flashList = new ArrayList<>();

SQLiteDatabase database = this.getReadableDatabase();

Cursor cursor = database.rawQuery(flashcardSearchQuery, null);

so when I search for 'all', it should match these:

all  
mall  
all or nothing

but not these:

all, whole  
recall, wow

But I get nothing. Similar when I try w/o the '^' and just "[,]%" nothing matches, what am I missing ?

I see this output in the debugger, so the concatenation seems to work, right ?

SELECT * FROM words where english_word LIKE '%little[^,]%'

[UPDATE] Great, both suggestions (REGEXP/GLOB) work:

String flashcardSearchQuery = "SELECT * FROM " + TABLE_NAME + " where " + COLUMN_ENGLISH_WORD +
        " GLOB " + "'*[^a-zA-Z]" + search_str + "[^a-zA-Z]*'";

String flashcardSearchQuery = "SELECT * FROM " + TABLE_NAME + " where " + COLUMN_ENGLISH_WORD +
        " REGEXP " + "'.*[^a-zA-Z]" + search_str + "[^a-zA-Z].*'";

Now I only have to figure out how to include cases where the search term is the final word e.g.

all
this is all

Funny, many references do seem to suggest that LIKE should work with [] ...

[2ND UPDATE] I just settled for a 2 liner, though not versed enough in regexp to understand why it covers even the 'start' permutations:

String flashcardSearchQuery = "SELECT * FROM " + TABLE_NAME + " where " +
        COLUMN_ENGLISH_WORD + " REGEXP '.*[^a-zA-Z]" + search_str + "[^a-zA-Z].*' OR " +
        COLUMN_ENGLISH_WORD + " REGEXP '.*[^a-zA-Z]" + search_str + "'";

AFAIK, `LIKE` does not support the syntax you are trying. See http://sqlite.org/lang_expr.html. — CommonsWare, Jul 15 '17 at 18:39
but they had an accepted answer here, using LIKE, no ? https://stackoverflow.com/questions/5444300/search-for-whole-word-match-with-sql-server-like-pattern — Mairyu, Jul 15 '17 at 19:21

score 2 · Answer 1 · answered Jul 15 '17 at 18:43

2

May be this should work, i.e adding two where clauses with logical and to satisfy both, one for search string and other for no comma

String flashcardSearchQuery = "SELECT * FROM " + TABLE_NAME + " where " + COLUMN_ENGLISH_WORD + " LIKE " + "'%" + search_str + "%' AND "  + COLUMN_ENGLISH_WORD + " NOT LIKE '%,%'";

answered Jul 15 '17 at 18:43

DhruvJoshi

17,041
6
41
60

that was my fallback, but I want to cover a lot of exception, that would make this one too tedious – Mairyu Jul 15 '17 at 19:26
In that case I'd suggest is to put all your exceptions in a table. Join to this table to exclude rows. – DhruvJoshi Jul 16 '17 at 04:28

score 1 · Answer 2 · answered Jul 15 '17 at 18:42

1

The LIKE operator does not support character ranges.

Use GLOB instead:

... WHERE EnglishWord GLOB '*little[^,]*'

answered Jul 15 '17 at 18:42

CL.

173,858
17
217
259

Maciej · Accepted Answer · 2017-07-15T18:57:52.783

0

LIKE is not strong enough to use regex.

Try using REGEXP

Select * from table_name where some_string REGEXP '.*all[^,\.]*'

edited Jul 15 '17 at 18:57

answered Jul 15 '17 at 18:44

Maciej

149
1
5

Just add a space before the search term to make it a final word – Maciej Jul 15 '17 at 21:44
sorry, not sure where, in front of 'all' ? How would that work ? I could probably solve it with an ``OR`` but I'm hoping for a single expression to cover all my bases. – Mairyu Jul 15 '17 at 22:24

SQLite query with [^] not working

3 Answers3