Since it seems you know regex, you might want to look at the free 2Empower Text Toolkit plugin; it handles regex.
I was recently up against this very same issue, and will be curious to see if anyone has an elegant solution.
I was doing a variation to what you have above:
WHERE UPPER( ' ' || " & Quote( varFieldName ) & " || ' ' ) LIKE ?
And then my substitution parameter in the ExecuteSql call was:
"% " & Upper( varMatchString ) & " %"
I discarded this as inadequate, however, once I realized that I still needed to take into account other word separator characters, e.g. punctuation marks, etc..
To date, I haven't come up with anything that I like. (pun not intended)
p.s. I enjoy a lot of your posts here. Very good stuff.
Thank you both Nick and Steve.
Unfortunately, a RegEx plugin can not be applied in this case, since the query must be done with ExecuteSQL() only. What I have shown above will be only a small fragment of a larger, dynamically created SQL query that will be used to query a database structure which in some parts has a key-value-like data model. Something like a FileMaker-Find-like-to-internal-data-structure-translator, and also multilingual (which is typically Swiss).
Well, I think the calculation I'm working on will need a few more intermediary steps.