tipem — 2008-12-06T13:14:04-05:00 — #1
Is there any way to COUNT regexp matches in MySQL and store it in a column in the results set?
SELECT name, COUNT(REGEXP '[ae]') as matches FROM table
If so, could you provide a couple of examples? I need to do something like this for sorting purposes.
wibble_wobble — 2008-12-06T13:58:47-05:00 — #2
Cant tell what you are trying to match with regex, but this should work:
SELECT SUM(name REGEXP '[ae]') AS matches FROM table
(I use 'SUM' because 'name REGEXP exp' returns 1 or 0.)
r937 — 2008-12-06T15:15:43-05:00 — #3
actually, the fact that REGEXP returns 0 or 1 (instead of TRUE or FALSE) is in my opinion not sufficient reason to sum these values directly
it's better to write it as a CASE expression -- it's more obvious, and easier to swap with some other relevancy expression should you ever want something other than a REGEXP test
CASE WHEN name REGEXP '[ae]'
ELSE 0 END
) AS matches
tipem, you might also be interested in this, if you're looking to sort by relevance...
Simple Keyword Relevance
wibble_wobble — 2008-12-06T15:35:56-05:00 — #4
Tipem: This is why r937 deserves your vote in the SP Awards for SQL guru
One thing I'm wondering: you want to use CASE for clarity and maintainability. Why not just give the query a comment saying "Sums because REGEXP returns 1 or 0". Over a largish data set, surely the case statements would slow it down quite a bit?
tipem — 2008-12-07T11:27:31-05:00 — #5
You guys truly rock my socks! Awesomesauce!
I have run into a problem though... I am trying to find all of the following instances and then count them (yes, with the square brackets included): [a], [e], [p], [l], [d], [w], and [f]. The regexp that I am using is \[[aefdwpl]\] to create this query (obviously this query is just to test on one row):
SELECT w_attack, SUM(
CASE WHEN w_attack REGEXP '\\[[aefdwpl]\\]'
ELSE 0 END
) AS attack_icons
FROM weapons WHERE w_id = 1 GROUP BY w_id
But unfortuately it is returning only 1 for attack_icons (when in fact, there are 23 matches). Does the MySQL REGEXP function match everything? Or just find one match and then stop? Let me know... how should I go about fixing this problem?
Thanks in advanced! Again, you guys rock! I should nominate r937 for SQL guru...
wibble_wobble — 2008-12-07T13:58:18-05:00 — #6
MySQL doesnt support regex replacements (which is what you would need: replace other chars with nothing, then count the resulting length). Do this in your programming/scripting language and store the result in a new field ('attack_icons_count' maybe).
Edit: If you need help with it, let me know and I'll do a blog post on it.
r937 — 2008-12-08T11:19:25-05:00 — #7
CASE WHEN w_attack IN ( '[a]', '[e]', '[p]', '[l]', '[d]', '[w]', '[f]' )
ELSE 0 END ) AS attack_icons