MySQL query to extract first word from a field

Question

Hi,

I would like to run a query that returns the first word only from a particular field, this field has multiple words separated by spaces, I assume I may need to carry out some regex work to accomplish this? I know how to do this using a few ways in PHP but this would best be carried out on the database side. Any ideas much appreciated. Thanks.

Answer 1

Substring_Index: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index

SELECT SUBSTRING_INDEX(name,' ',1) ...

Answer 2

Here you go :)

SELECT SUBSTRING_INDEX( `field` , ' ', 1 ) AS `field_first_word`
FROM `your_table`

Answer 3

SELECT
  SUBSTR(field_name, 1, LOCATE(' ', field_name)) AS first_word
FROM
  table

Answer 4

select 
    substring(test_field, 1, instr(test_field, ' ')) 
from 
    test_table