[thelist] MySQL extracting a teaser from a text field

[Matt Warden]

-----BEGIN PGP SIGNED MESSAGE-----
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?

Yeah:

SELECT SUBSTRING( body, 1, LOCATE('.', concat(body,'.'), '10') ) AS
teaser
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
http://mattwarden.com


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

iD8DBQFDClSsAQ0d4HGyPE8RAtD3AJ47TOvkrJL4U746Iv+MbYnZ0/ihAQCfQ/e+
IR0Y03YhjA2RgiYPizDRbjY=
=isiC
-----END PGP SIGNATURE-----