Possible to delete everything after a 'space' in MySQL field?

Question

Let's assume I have the following field:

' name sub _blah '

I can get rid of the outside spaces with the TRIM() function but I am actually looking to remove everything after the first space (after the trim).

so:

'name sub _blah' would turn into 'name'

I know this is possible in PHP but I am trying to do on a MySQL only call. Is there a function I do not know about for this?

Thanks!

Solution 1

select substring_index('name sub _blah',' ',1)

Solution 2

Try this:

SUBSTRING_INDEX(str,' ', 1)

Or, if you still need TRIM as well:

SUBSTRING_INDEX(TRIM(str),' ', 1)

See e.g. here for additional documentation.

Comments

[JM4]

thanks for the answer. I appreciate the additional links as well

[phooji]

+1 For same answer, but 30 seconds sooner. Well played, nick :)

[Nicola Cossu]

Hi phooji. + 1 even for you ;)

[Thomas Williams]

This saved my life +1