Re: [PHP-DB] SELECTING a sentence from a text field?

[mike]

Here is what I use to select the first sentence from my text fields.

$result = mysql_query("SELECT news_id, headline, substring_index(body, 
\".\" ,1) AS body FROM news",$db);


Mike

::: Larry French ::: wrote:

> Hello all. I have a MySQL field called 'description' that contains a
> paragraph of text. I want to pull out the first 30 words (or there about)
> from 'description'.
> 
> This is what I came up with:
> 
> "SELECT
> ID,
> name,
> LEFT(description,95),
> IF(enrolling = 1,'Currently enrolling','')
> FROM trials
> ORDER BY name ASC
> LIMIT $offset, $limit"
> 
> Obviously this grabs the first 95 characters from 'description', but when I
> display 'description' the word at the end of the string (after the 95th
> charter) is often cut right in the middle.
> 
> I know that I could select the whole field then use a regex to chop the
> string at the first '.' but I would rather have a SQL statement that only
> grabs the text I need.
> 
> So how do I write a SQL statement that will grab the first 30 words without
> chopping the last word?
> 
> Thanks for your time.
> larry.
> 
> 
> 
> 
> 


-- 
Mike


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP-DB] SELECTING a sentence from a text field?

[Beau Lebens]

Larry, take a look in the MySQL manual (or other SQL reference, but MySQL
has it there)

String Functions
http://www.mysql.com/doc/S/t/String_functions.html

Particularly, look at (no named anchors that I can see :)
LOCATE(substr,str,pos)

ie, you could use perhaps
LOCATE(" ", yourField, 95)

?

HTH

Beau

// -Original Message-
// From: ::: Larry French ::: [mailto:[EMAIL PROTECTED]]
// Sent: Tuesday, 14 May 2002 8:29 AM
// To: [EMAIL PROTECTED]
// Subject: [PHP-DB] SELECTING a sentence from a text field?
// 
// 
// Hello all. I have a MySQL field called 'description' that contains a
// paragraph of text. I want to pull out the first 30 words (or 
// there about)
// from 'description'.
// 
// This is what I came up with:
// 
// "SELECT
// ID,
// name,
// LEFT(description,95),
// IF(enrolling = 1,'Currently enrolling','')
// FROM trials
// ORDER BY name ASC
// LIMIT $offset, $limit"
// 
// Obviously this grabs the first 95 characters from 
// 'description', but when I
// display 'description' the word at the end of the string 
// (after the 95th
// charter) is often cut right in the middle.
// 
// I know that I could select the whole field then use a regex 
// to chop the
// string at the first '.' but I would rather have a SQL 
// statement that only
// grabs the text I need.
// 
// So how do I write a SQL statement that will grab the first 
// 30 words without
// chopping the last word?
// 
// Thanks for your time.
// larry.
// 
// 
// 
// 
// -- 
// PHP Database Mailing List (http://www.php.net/)
// To unsubscribe, visit: http://www.php.net/unsub.php
// 

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php