Retrieve first sentence of article stored in MySQL table

Question

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?

Answer 1

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);

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.