How to extract the nth word and count word occurrences in a MySQL string?

by

The following is a proposed solution for the OP’s specific problem (extracting the 2nd word of a string), but it should be noted that, as mc0e’s answer states, actually extracting regex matches is not supported out-of-the-box in MySQL. If you really need this, then your choices are basically to 1) do it in post-processing on the client, or 2) install a MySQL extension to support it.

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

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

As a working example, I used:

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 successfully extracts the word IS

More Related Contents:

  1. How to count words in MySQL / regular expression replacer?
  2. How to do a regular expression replace in MySQL?
  3. MySQL Split Comma Separated String Into Temp Table
  4. MySQL strip non-numeric characters to compare
  5. How to extract two consecutive digits from a text field in MySQL?
  6. How to remove all non-alpha numeric characters from a string in MySQL?
  7. Using SQL to determine word count stats of a text field
  8. #1139 – Got error ‘repetition-operator operand invalid’ from regexp
  9. Does MySQL Regexp support Unicode matching
  10. How do I refer to capture groups in a MySQL regex?
  11. MySQL SELECT LIKE or REGEXP to match multiple words in one record
  12. Is it Possible to Enforce Data Checking in MySQL using Regular expression
  13. How to regex in a MySQL query
  14. remove all numeric characters from column mysql
  15. mySQL regex in the where clause
  16. How do I find the MySQL my.cnf location
  17. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  18. MySQL select one column DISTINCT, with corresponding other columns
  19. How to use mysql JOIN without ON condition?
  20. MySQL search and replace some text in a field
  21. Why is MySQL InnoDB insert so slow?
  22. Group mysql query by 15 min intervals
  23. MySQL – force not to use cache for testing speed of query
  24. MySQL FULL JOIN?
  25. Using Count to find the number of occurrences
  26. MYSQL select DISTINCT values in two columns
  27. MySQL Select ID’s which occur on different rows with multiple specific values for a column
  28. How can I achieve initcap functionality in MySQL?
  29. Array data type, split string,
  30. How can I make a table in MySQL called “order”?

Leave a Comment