MySQL: Read out first word of a column

What you are looking for, is the MySQL function SUBSTRING_INDEX(), which can be used in the following manner:

SELECT SUBSTRING_INDEX(col, ' ', 1) FROM tab WHERE ...

The function expects 3 parameters:

• A string or the name of the column (in this case "col"), which should be read out.
• The character, at which the string should be cut off. Because you are searching for the first word, this character is a space ' '.
• The number of occurences of the character. Because you want to cut at the first word, we cut at the first occurence of ' ' and we are writing "1" here. If you would like to read out the first two words, you had to write "2" here, because we had to cut at the second occurence of the space.

This example reads out the characters left from the position of the space. If you want to read out the characters standing right from the position of the space, you need to make the number negative.
Last update on 2020-11-01 | Created on 2012-03-12