Get all characters before space in MySQL

Question

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

For example, if field1 is "chara ters"

I want him to return "chara"

What does this select statement look like?

Answer 1

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

Note that if the specified string does not contain spaces, this returns an empty string.

Answer 2

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

This returns 'chara':

 SELECT SUBSTRING_INDEX( field1, ' ', 1 ) 

This returns 'ters':

 SELECT SUBSTRING_INDEX( field1, ' ', -1 ) 

More details

A positive value will look for your specified character at the beginning of the line, and a negative value will begin at the end of the line. The number value indicates the amount of your specified character to search before returning the rest of the string. If the character you are looking for does not exist, the entire value of the field is returned.

In this case, a -2 will return everything to the right of the second to the last space that does not exist in this example, so the entire field value will be returned.

Answer 3

To do this, you will need string operations . Assuming each field has at least one whitespace 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;