MySQL Select as if tokenized using Regexp

Question

I'm looking for a string within the products model field that should be preceded or followed by a space, unless it's an exact match.

I can specifically use the four possible permutations by saying

SELECT products_id, products_model FROM products WHERE products_model LIKE '% UMA12' OR products_model LIKE 'UMA12 %' OR products_model LIKE '% UMA12 %' OR products_model = 'UMA12';

but is there a regexp I can use to express this more succinctly?

This query should match

FOO UMA12 BAR
UMA12
UMA12 FOO 
FOO UMA12

but not

UMA1 
FUMA1

etc.

See [MySQL REGEXP word boundaries \[\[:<:\]\] \[\[:>:\]\] and double quotes](https://stackoverflow.com/questions/18901704/mysql-regexp-word-boundaries-and-double-quotes) — Wiktor Stribiżew, Apr 18 '21 at 09:54

score 2 · Accepted Answer · answered Apr 18 '21 at 09:49

If you want to find UMA12 as a standalone word, then use REGEXP with word boundaries:

SELECT *
FROM products
WHERE products_model REGEXP '[[:<:]]UMA12[[:>:]]';

Note: On MySQL 8+, you'll need to use slightly different syntax for REGEXP with word boundaries:

SELECT *
FROM products
WHERE products_model REGEXP '\\bUMA12\\b';

MySQL Select as if tokenized using Regexp

1 Answers1