[thelist] MySQL extracting a teaser from a text field

Matt Warden mwarden at gmail.com
Mon Aug 22 17:49:57 CDT 2005

Hash: SHA1

Noah St.Amand wrote:
> 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
> option.
> Any ideas?


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

or maybe:

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

or maybe:

SELECT (CASE LOCATE('.', body, '10')
        WHEN 0 THEN body
        ELSE SUBSTRING( body, 1, LOCATE('.',body,'10') )
        END) as teaser
FROM table
WHERE id = 1;

...depending on exactly how you want it to work.

- --
Matt Warden
Miami University
Oxford, OH, USA

This email proudly and graciously contributes to entropy.
Version: GnuPG v1.2.4 (GNU/Linux)


More information about the thelist mailing list