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`

Nifty.

Related Posts:

About Craig Lotter

Software developer, husband and dad to two little girls. Writer behind An Exploring South African. I don’t have time for myself any more.