CODEWITHSUNDEEP
pythonmysql
vicarus99
vicarus99

Reputation: 59

MYSQL - Return pattern that is matched against

In the below example query I'm selecting a phrase(e.g. "I like dogs") where it matches to at least one entry in a set of keywords (e.g. "dogs | cats"). My question is whether it is possible to extract the keyword(s) that the phrase matches to. So, in the above "dogs" is returned but if the phrase was "I Like dogs and cats" "dogs" and "cats" is returned.

select phrase from animals where phrase REGEXP (SELECT GROUP_CONCAT(kw SEPARATOR '|') from keywords )

Upvotes: 0

Views: 27

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522752

You can do this fairly easily in MySQL 8+ with REGEXP_REPLACE:

SELECT REGEXP_REPLACE(input, '^.*\\bI like (dogs|cats)\\b.*$', '$1') AS output
FROM yourTable;

Upvotes: 1

Related Questions