MySQL PREG_CAPTURE regex

Question

I have the following example:

SELECT
    PREG_CAPTURE('/(#[^ ]*)/i', sentence, 1, 1) AS result_1,
    PREG_CAPTURE('/(#[^ ]*)/i', sentence, 1, 2) AS result_2

FROM (
    SELECT 'Hello #world #everyone' AS sentence
) tmp

Returning:

result_1 | result_2
---------|-----------
#world   | #everyone

As you can see, PREG_CAPTURE is:

PREG_CAPTURE(pattern, subject [, capture-group] [, occurence])

All I need is to return all ocurrences until the result is NULL on the same column. So, instead of having several result_x columns, I would have only one column result. Also, I need to group these results and count them on a separate column.

So the result would look like:

result    | count
----------|-------
#world    | 1
#everyone | 1

How can I accomplish that?

I'm using the following library for regex usage on mysql: https://github.com/mysqludf/lib_mysqludf_preg

Answer 1

MySQL follows a basic convention, which is: SELECT's can only select a part of a given subset, it can never create more lines that what has been given to him. So if you return 10 lines on your clause, SELECT statements cannot produce 15 lines of output.

Another option I've thought was using basic regexs on your WHERE clause and then splitting single line results into multiple columns, but that is basically what PREG_CAPTURE (a user defined function) has already provided to you.

Sorry I couldn't help any further.