98

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?

Wolfie
  • 27,562
  • 7
  • 28
  • 55
Alex Gordon
  • 57,446
  • 287
  • 670
  • 1,062

4 Answers4

149

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

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

ᴍᴇʜᴏᴠ
  • 4,804
  • 4
  • 44
  • 57
Mchl
  • 61,444
  • 9
  • 118
  • 120
  • 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 Nov 13 '14 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 Jun 27 '17 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 Jun 27 '17 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 Jun 28 '17 at 09:53
  • @SwapnilGangrade if needed: use concat to add a space at the end – Rinzwind Sep 19 '18 at 11:38
  • 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 Dec 10 '19 at 15:55
107

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.

T. Brian Jones
  • 13,002
  • 25
  • 78
  • 117
  • 3
    This also works, if there are strings, without a blank, for example 'chara' will also work. – bernhardh Mar 19 '15 at 19:48
  • 2
    This worked great in getting the major + minor version of a version string with x.y.z components. – thomthom May 06 '16 at 20:22
  • 4
    This works best when you want the entire field returned if the character could not be found. – stephentgrammer Feb 27 '17 at 17:38
  • I use this to extract geo coordinates from a string field where a delimiter (comma) is guarateed to exists. – ob-ivan Feb 02 '18 at 08: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 Jul 20 '22 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;
soulmerge
  • 73,842
  • 19
  • 118
  • 155
  • +1 for safe version, although it's not clear what result should be returned when field does not contain a blank. – Mchl Aug 12 '10 at 19:26
  • 2
    had to change 0 for 1: `SELECT SUBSTR(field1, 1, LOCATE(' ', field1)) FROM your_table;` – Lluís Feb 26 '14 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).

enharmonic
  • 1,800
  • 15
  • 30