105

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?

Improve this question

4 Answers 4

Reset to default
156

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

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

Improve this answer
6
  • 7
    It return 'chara ' with trailing space. You need decrease result of LOCATE if not need separator in the end: SELECT LEFT(field1, LOCATE(' ', field1) - 1)
    – Enyby
    Commented Nov 13, 2014 at 13:56
  • 3
    This will not work if your main field does not contains space . Substring will return empty value instead of returning whole string. E.g. if my field contains string "test-possible-incorrect-name" substring will return nothing instead of "test-possible-incorrect-name".
    – Swapnil Gangrade
    Commented Jun 27, 2017 at 10:29
  • 2
    @SwapnilGangrade Depending on actual use case in might be expected behaviour or not. One might argue that if there is no space in the string, then there are no characters before space, and so an empty string should be returned. Another person might want to have an entire string returned in such case. The question does not specify how the function should behave in this edge case. I will update the answer to highlight it.
    – Mchl
    Commented Jun 27, 2017 at 14:16
  • 14
    Then select SUBSTRING_INDEX( field1, ' ', 1 ) from table; will be choice if we want to have a complete string if there is no space.
    – Swapnil Gangrade
    Commented Jun 28, 2017 at 9:53
  • 4
    The answer below (SUBSTRING_INDEX) is "better" as a general solution because it will retain values that don't contain the delimiter, instead of resulting in a blank value.
    – degenerate
    Commented Dec 10, 2019 at 15:55
109

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.

Improve this answer
5
  • 3
    This also works, if there are strings, without a blank, for example 'chara' will also work.
    – bernhardh
    Commented Mar 19, 2015 at 19:48
  • 2
    This worked great in getting the major + minor version of a version string with x.y.z components.
    – thomthom
    Commented May 6, 2016 at 20:22
  • 4
    This works best when you want the entire field returned if the character could not be found.
    – stephentgrammer
    Commented Feb 27, 2017 at 17:38
  • I use this to extract geo coordinates from a string field where a delimiter (comma) is guarateed to exists.
    – ob-ivan
    Commented Feb 2, 2018 at 8:59
  • If using this to parse out numbers (such as in the string major/minor version suggestion), remember to cast to the correct data type before running certain aggregations. For instance, if you expect to be dealing with integers, but you forget to cast, the string "9" will actually be greater than "72", resulting in some very confusing outputs.
    – bsplosion
    Commented Jul 20, 2022 at 21:29
12

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;
Improve this answer
2
  • +1 for safe version, although it's not clear what result should be returned when field does not contain a blank.
    – Mchl
    Commented Aug 12, 2010 at 19:26
  • 2
    had to change 0 for 1: SELECT SUBSTR(field1, 1, LOCATE(' ', field1)) FROM your_table;
    – Lluís
    Commented Feb 26, 2014 at 11:36
0

For a generalized approach that returns the nth value (one-based index) from a column containing delimited values:

select SUBSTRING_INDEX(SUBSTRING_INDEX(my_column,@delimiter,@index),@delimiter,-1) from my_table;

This will return the entire column contents if the delimiter does not exist, and the last field if the index value exceeds the number of fields. For instance, if my_column contains 'foo bar baz' and you want the 2nd field:

select SUBSTRING_INDEX(SUBSTRING_INDEX(my_column,' ',2),' ',-1) from my_table;

will return bar, and if you specify the 4th field (which does not exist):

select SUBSTRING_INDEX(SUBSTRING_INDEX(my_column,' ',4),' ',-1) from my_table;

will return baz (the last field).

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.