Substring with dots

I am using SUBSTRING function to retreive an "excerpt" of a message body:

SELECT m.id, m.thread_id, m.user_id, SUBSTRING(m.body, 1, 100) AS body, m.sent_at
FROM message m;

What I would like to do is add 3 dots to the end of the substring, but only if the source string was more than my upper limit (100 characters), i.e. if substring had to cut off the string. If the source string is less than 100 characters then no need to add any dots to the end.

I am using PHP as m开发者_如何转开发y scripting language.

---

That can be done in the query, rather than PHP, using:

SELECT m.id, m.thread_id, m.user_id, 
       CASE 
         WHEN CHAR_LENGTH(m.body) > 100 THEN CONCAT(SUBSTRING(m.body, 1, 100), '...')
         ELSE m.body
       END AS body, 
       m.sent_at
  FROM MESSAGE m

The term for the three trailing dots is "ellipsis".

---

Ask for 101 characters. If you receive 101 characters your resource string is definitely more than 100 characters. In that case, remove the last character in your scripting language of choice and add "...". This will relieve your DB somewhat.

Personally I would advise you to create a bit of a difference though. E.g. cut off at 90 characters if and only if you exceed 110 characters (by requesting 110 + 1 characters of course). Otherwise you will get the effect I notice with Slashdot sometimes: you have a Read the rest of this comment link, only to receive the final word of the story.

More or less, the user will be annoyed if the method of retrieving the rest of the story takes more space than the story itself.