MySQL: How to Select the First Word of a Sentence using SQL CodeUnit 21 MAY 2012

Sometime it is useful to be able to extract the first word of a sentence contained in one of the columns of your table. Luckily for us, MySQL makes this a trivial operation thanks to its useful SUBSTRING_INDEX() function.

From the reference manual:

SUBSTRING_INDEX(str,delim,count) – Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.

So for our purposes, if we want to select the first word from a sentence or string of words, the natural delimiter we would be looking out for is ‘ ‘ – i.e. an empty space character. Because we’re interested in the first word of the sentence, we want everything returned before the first occurrence of the space, leaving us with sql which looks like this:

SELECT SUBSTRING_INDEX( `myColumn` , ' ', 1 ) as `firstWord` FROM `sentences`


Related Posts:

About Craig Lotter

South African software architect and developer at Touchwork. Husband to a cupcake baker and father to two little girls. I don't have time for myself any more.