How to achieve word boundary in Sqlite Android?

Question

I would like to achieve the following (a full text search),

SELECT * FROM tablename where columnname REGEXP '[[:<:]]some string[[:>:]]'

Where i am interested in only exact strings (not just words) from a full text column.

I have been using the exact SQL above in MySQL and now migrating most of the code to android apps.

But I have been looking at various posts where it is mentioned that REGEXP isn't supported in Android Sqlite (for example: link1, link2, link3 ).

Is there a way to enable REGEXP in Android?

If not are there any alternatives for the above SQL?

Thank you,

EDIT: Currently I am receiving the following exception when using REGEXP in Android,

android.database.sqlite.SQLiteException: no such function: REGEXP (code 1):...

I understand we can make use of GLOB and LIKE ( or may be even MATCH ). How can columnname REGEXP '[[:<:]]somestring[[:>:]]' be converted to make use of GLOB and/or LIKE and/or MATCH?

SOLUTION 1: With @cybersam's help following is what I am using (after some modifications)

SELECT * FROM tablename where
  (columnname GLOB '*some string*' OR columnname GLOB '*Some string*') AND 
(
(
    (columnname GLOB '*[^a-zA-Z0-9_]some string[^a-zA-Z0-9_]*' AND
    columnname GLOB '*some string[^a-zA-Z0-9_]*' AND
    columnname GLOB '*[^a-zA-Z0-9_]some string*')
OR
    (columnname GLOB '*[^a-zA-Z0-9_]Some string[^a-zA-Z0-9_]*' AND
    columnname GLOB '*Some string[^a-zA-Z0-9_]*' AND
    columnname GLOB '*[^a-zA-Z0-9_]Some string*')
)
)

GLOB is case-sensitive so I have an additional OR

@cybersam's second solution is much faster in my case.

SOLUTION 2: To handle case in-sensitivity

SELECT * FROM tablename where
  (columnname GLOB '*[sS][oO][mM][eE] [sS][tT][rR][iI][nN][gG]*') AND 
(
    (
    columnname GLOB '*[^a-zA-Z0-9_][sS][oO][mM][eE] [sS][tT][rR][iI][nN][gG][^a-zA-Z0-9_]*' AND 
    columnname GLOB '*[sS][oO][mM][eE] [sS][tT][rR][iI][nN][gG][^a-zA-Z0-9_]*' AND 
    columnname GLOB '*[^a-zA-Z0-9_][sS][oO][mM][eE] [sS][tT][rR][iI][nN][gG]*')
)

Nakamura

try this \<somestring\> – Photometry

Answer 1

To actually support REGEXP, you will have to add your own regexp() user function.

This link might help you figure out how to create user defined functions, in general, for Android -- but it is not simple.

If your patterns are very simple, the GLOB operator might be good enough.

For example, to perform a search equivalent to this MYSQL query:

SELECT * FROM tablename where columnname REGEXP '[[:<:]]some string[[:>:]]'

you can try this in SQLite:

SELECT * FROM tablename where
  columnname GLOB '[^a-zA-Z0-9_]some string[^a-zA-Z0-9_]' OR
  columnname GLOB 'some string[^a-zA-Z0-9_]' OR
  columnname GLOB '[^a-zA-Z0-9_]some string' OR
  columnname GLOB 'some string';

The above query uses the fact that in MYSQL, a word character is defined to be either an alphanumeric character or an underscore. The additional OR terms are needed to also match the cases where there is no text on either (or both) sides of some string.

Finally, if 'some string' is relatively rare in columnname, then the following longer query may actually be faster, since it will only do multiple GLOB evaluations for a minority of values:

SELECT * FROM tablename where
  columnname GLOB '*some string*' AND (
    columnname GLOB '[^a-zA-Z0-9_]some string[^a-zA-Z0-9_]' OR
    columnname GLOB 'some string[^a-zA-Z0-9_]' OR
    columnname GLOB '[^a-zA-Z0-9_]some string' OR
    columnname GLOB 'some string');

Geodetic