[thelist] MySQL extracting a teaser from a text field

Noah St.Amand noah at tookish.net
Mon Aug 22 16:27:45 CDT 2005

I'm trying to find the best way to extract a teaser from a text field in 
MySQL. Most of the time I can just take the first sentence, like this:

SELECT SUBSTRING(body, 1, LOCATE('.',body,'10')) AS teaser FROM table 
WHERE id = 1;

That finds the first period more than ten characters in, and returns 
everything up to and including that period.

The problem is that the field doesn't always contain a period, in which 
case this query comes back empty.

I could run a separate query to look for a period before I execute this 
query, then if no period is found just select a certain number of 
characters, but that would require a bunch of PHP, and I'd rather do it 
all in the SQL if possible.

Unfortunately this has to work in MySQL 3.23, so subqueries aren't an 

Any ideas?


More information about the thelist mailing list