[thelist] MySQL extracting a teaser from a text field

[Jason Handby]

Hi Noah,


> 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.

A bit hacky, but you could do

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

If there's no period in body, body + '.' is still guaranteed to have
one.


HTH



Jason