I have a table with over a thousand "keywords" defined, including keywords with spaces, and such, like:
1 | C# Developer
2 | C++
3 | R&D Expert
4 | President/Founder
5 | Kittens
Etc.
I need to match FROM this table to a large complex string (say a paragraph) to determine if any of the keywords exist within the text. For example, I need the "processing" of the below:
I am a C++ developer and a R&D Expert who loves Kittens!
To return
2 | C++
3 | R&D Expert
5 | Kittens
From what I can tell, I can't use MATCH AGAINST because even with quote marks, it won't do an EXACT match of words with spaces (e.g. "R&D Expert" - would pull matches that included "Expert", which I don't want) and I'm often needing to scan/match against dynamically written text (such as a paragraph from a resume).
I can write code that loops through each keyword (over 1,000) and scans the text (which can be well over 500 characters sometimes), but that would be ultra slow and inefficient compared to an internal function I may be overlooking.
Is there a way to accomplish what I'm trying to do besides cycling through each keyword from the DB and seeing if it exists (exactly word for word) in a blob of text?
How about a regexp approach? Assuming a table called keywords with column kw, and your long string in variable @longstring:
select k.*
from keywords k
where @longstring regexp concat('\\b', k.kw, '\\b')
This assumes that youw want to match on entire words: \b stands for a word boundary. This regex notation is supported in MySQL 8.0 only. In earlier versions, you would do:
select k.*
from keywords k
where @longstring regexp concat('[[:<:]]', k.kw, '[[:>:]]')
Of course, if you are satisfied with partial matches, like is good enough:
select k.*
from keywords k
where @longstring like concat('%', k.kw, '%')