CODEWITHSUNDEEP
androidregexsqlite
Mairyu
Mairyu

Reputation: 839

SQLite query with [^] not working

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 + "'";

Upvotes: 1

Views: 397

Answers (3)

Maciej
Maciej

Reputation: 147

LIKE is not strong enough to use regex.

Try using REGEXP

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

Upvotes: 0

DhruvJoshi
DhruvJoshi

Reputation: 17126

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 '%,%'";

Upvotes: 2

CL.
CL.

Reputation: 180070

The LIKE operator does not support character ranges.

Use GLOB instead:

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

Upvotes: 1

Related Questions