CODEWITHSUNDEEP
sqlsqlitesql-like
MirrorMirror
MirrorMirror

Reputation: 188

Sqlite match specific characters before wildcard

In transitioning from MS Access to Sqlite, I have a minor problem The following [,. ]% works well in MS Access for matching characters comma, period and space after a word but not in Sqlite.

For example for the dictionary phrases "aaa, bbb ccc" and "aaa, bbbbbb ccc" if the user types "aaa bbb", it becomes "aaa[,. ]%bbb" and then it goes into a SELECT * FROM mytable WHERE text LIKE ?. This doesn't work in Sqlite but it did in MS Access. Also I want it only to select the first dictionary phrase, not the second.

So, my question is how do I match a set of characters in Sqlite?

edit: I am not able to use regex's so I would like a solution without regexes

Upvotes: 0

Views: 290

Answers (2)

CL.
CL.

Reputation: 180300

In standard SQL, LIKE does not have character classes.

In SQLite, you can use GLOB instead:

... WHERE text GLOB 'aaa[,. ]*bbb'

(GLOB is case sensitive.)

Upvotes: 2

shA.t
shA.t

Reputation: 16978

AFAIK, You can't and you need to use REGEXP.

The best thing that I can figure is something like this:

select *
from t
where replace(replace(txt, ',', ''), '.', '') like 'aaa bbb%';

That will not cover aaa,bbbb or aaa bbbb.

Upvotes: 1

Related Questions