How to Extract the Second Word from a MySQL String Using String Functions?

Extracting Words and Counting Occurrences in MySQL Strings

Question:

You seek a MySQL query similar to the following:

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

Is there a syntax to extract text from expressions using regular expressions in MySQL?

Answer:

While extracting regex matches directly is not supported in default MySQL functionality, as mc0e mentions, there is a solution for extracting a specific word in a string using a combination of string functions:

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

Usage:

To extract the second word from a string, you can use the following query:

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

This will return the word "IS" as the output.

Note:

If extracting regex matches is a recurring need, consider post-processing the results on the client or installing a MySQL extension that supports regex extraction.

The above is the detailed content of How to Extract the Second Word from a MySQL String Using String Functions?. For more information, please follow other related articles on the PHP Chinese website!