Get all characters before space in MySQL

I would like to get all the characters in a field before a space

For example, if field1 is "chara ters"

I want it to return "chara"

What would this select statement look like?

SELECT LEFT(field1,LOCATE(' ',field1) - 1)

Note that if the string in question contains no spaces, this will return an empty string.

Below is another method that works and may seem a bit simpler to some. It uses the SUBSTRING_INDEX MySQL function. A 1 returns everything before the first space, and a -1 returns everything after the last space.

This returns 'chara':

SELECT SUBSTRING_INDEX( field1, ' ', 1 )

This returns 'ters':

SELECT SUBSTRING_INDEX( field1, ' ', -1 )

Details

A positive value will look for your specified character from the start of the string, and a negative value will start from the end of the string. The value of the number indicates the quantity of your specified character to look for before returning the remaining piece of the string. If the character you are searching for does not exist, the entire field value will be returned.

In this case, a -2 would return everything to the right of the second to last space, which doesn't exist in this example, so the entire field value will be returned.

You would need some string operations for that. Assuming every field has at least one space character:

SELECT SUBSTR(field1, 0, LOCATE(' ', field1)) FROM your_table;

Safe approach:

SELECT IF(
    LOCATE(' ', field1),
    SUBSTR(field1, 0, LOCATE(' ', field1)),
    field1
) FROM your_table;