Reputation: 61
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
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
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
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
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
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
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