Get the first sentence of an article stored in a 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 anyone help me out?

Answer 1

You can use the SUBSTRING_INDEX function:

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

If there is no dot in the text, it will return all the text.

Answer 2

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

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

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

Answer 3

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

For this you can use SUBSTRING_INDEX

You will probably need to combine it with REPLACE to take into account exclamation points (!) And question marks (?).

Answer 4

Clinton's answer is great, just make sure you bind this end of the sentence to a string:

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

Answer 5

You should search for any delimiters (.?!) With strpos() and find the position of the first occurrence. Thereafter:

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

Edit: You should take precautions when there is no delimiter by setting the default maximum length to show:

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

Answer 6

If you are sure that all sentences stop with a period ("."), You can quite easily handle regular expressions.

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

You must google the regular expression "your language." I think almost every high-level language supports regular expression.