Substring with dots

Question

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 my scripting language.

Answer 1

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".