Search code examples
sqlsqlitesql-like

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

Solution

  • 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.