GJ.
GJ.

Reputation: 61

Retrieve first sentence of article stored in MySQL table

I need to display the first sentence (up to the first full stop) of each article in my database, using MySQL.

Can someone please help me?

Upvotes: 4

Views: 1742

Answers (6)

Phil LaNasa
Phil LaNasa

Reputation: 3035

Clinton's answer is great, just make sure you tack that end-of-sentence period back onto the string:

SELECT CONCAT(SUBSTRING_INDEX('Here is my text. Hope this works!', '.', 1), '.');

Upvotes: 1

Clinton
Clinton

Reputation: 2837

You can use the SUBSTRING_INDEX function:

SELECT SUBSTRING_INDEX('Here is my text. Hope this works!', '.', 1);

If there's no dot in your text, it will return the whole text.

Upvotes: 6

Soufiane Hassou
Soufiane Hassou

Reputation: 17750

It seems that you want to that directly from your SQL query.

To do that, you can use SUBSTRING_INDEX

You will probably need to combine it with REPLACE to take in consideration exclamation (!) and question (?) marks.

Upvotes: 1

a'r
a'r

Reputation: 37009

This is a naive approach to your problem. However, it relies on every full stop having a space immediately afterwards.

select substr(article, 0, instr(article, '. ')) from articles_table;

If you need it to be more reliable, then you would probably need a regular expression.

Upvotes: 1

Christopher
Christopher

Reputation: 1

If you're sure all the sentences stop with a dot ('.') you can probably achieve that pretty easily with regular expressions.

http://www.regular-expressions.info/

You should google "regular expression 'your language'". I think pretty much every high level language has regular expression support.

Upvotes: -1

chelmertz
chelmertz

Reputation: 20601

You should search for any delimiters (. ? !) with strpos() and find the position of the first occurence. After that:

$first_sentence = substr($your_sentence, 0, $first_occurance+1);

Edit: you should take into precaution when there is no delimiter, by setting a default max length to show:

$max_length = 40;
$stop = $first_occurance > $max_length ? $max_length : $first_occurance + 1;
$first_sentence = substr($your_sentence, 0, $stop);

Upvotes: -1

Related Questions