Mysql: Use REGEX to extract string

Question

I would like to have a mysql query like this:

select <second word in text> word, count(*) from table group by word;

All the regex examples in mysql are used to query if the text matches the expression, but not to extract text out of an expression. Is there such a syntax?

Answer 1

I don't think such a thing is possible. You can use substring function to extract the part you want.

Answer 2

No, there isn't a syntax for extracting text using regular expressions. You have to use the ordinary string manipulation functions.

Alternatively select the entire value from the database (or the first n characters if you are worried about too much data transfer) and then use a regular expression on the client.

Answer 3

According to http://dev.mysql.com/ the SUBSTRING function uses start position then the length so surely the function for the second word would be: SUBSTRING(sentence,LOCATE(' ',sentence),(LOCATE(' ',LOCATE(' ',sentence))-LOCATE(' ',sentence)))?

Answer 4

BenWells has it very almost correct. Working from his code, here's a slightly adjusted version:

SUBSTRING(sentence,LOCATE(' ',sentence),LOCATE(' ',sentence,(LOCATE(' ',sentence)+1))-LOCATE(' ',sentence))

As a working example, I used:

SELECT SUBSTRING(sentence,LOCATE(' ',sentence),LOCATE(' ',sentence,(LOCATE(' ',sentence)+1))-LOCATE(' ',sentence))
FROM (SELECT 'THIS IS A TEST' AS sentence) temp

This successfully extracts the word IS

@BenWells If this turns out to be the correct answer, you should update your code to get the "accepted answer"