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?
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.
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.
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.
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), '.');
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);
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.
精彩评论