MySQL truncate text with ellipsis

Question

Suppose I have a MySQL table of one column: "Message". It is of type TEXT. I now want to query all rows, but the text can be large (not extremely large but large) and I only want to get a summary of them. For example the result can be populated into a list.

Is there a way to trim the text to a specific length (say, 10 characters), and add ellipsis if the text is trimmed?

For example:

Message
-----------
12345678901234
1234567890
12345
12345678901

Query result:

1234567...
1234567890
12345
1234567...

Thanks!

Pluto

Answer 1

select case when length(message) > 7 
then concat(substring(message, 1, 7), '...')
else message end as adapted_message
from ...

to test/confirm:

SELECT CASE WHEN LENGTH('1234567890') > 7 
THEN CONCAT(SUBSTRING('1234567890', 1, 7), '...') 
ELSE '1234567890' END AS adapted_message

UNION  

SELECT CASE WHEN LENGTH('12345') > 7 
THEN CONCAT(SUBSTRING('12345', 1, 7), '...') 
ELSE '12345' END AS adapted_message

Crystallography

You may want to use char_length() instead of length() to ensure the truncation occurs at the same amount of characters... length() returns the string length in bytes and char_length() returns the length in characters. – Gustie

There is a VERY common but rather annoying mistake in this answer. It should be when length(message) > 10. Otherwise the produced 'truncated' text might be LONGER than the original text. I see this all the time in all applications, even Facebook. Don't you hate it that you have to click "more" just to see two letters? "More" has 4! – Slightly

Answer 2

Here's a simple one line solution:

IF(CHAR_LENGTH(message) > 10, CONCAT(LEFT(message, 7),"..."), message)

Washin

Answer 3

or...

SELECT CONCAT(LEFT(message, 7), IF(LENGTH(message)>7, "…", ""))
FROM table

Obara

Great answer! You may want to replace the ellipsis character with three periods though, as not all character encodings will support that character. – Gustie

This is a good approach, but works incorrectly when the field is between 7 and 10 characters long. – Cognation

Answer 4

You can declare a new ELLIPSIS function in order to make your query readable:

DELIMITER //

CREATE FUNCTION ELLIPSIS ( str TEXT, max_length INT )
RETURNS TEXT

BEGIN
   DECLARE str_out TEXT;

   IF LENGTH(str) <= max_length THEN
      SET str_out = str;
      
   ELSE
      SET str_out = CONCAT(SUBSTR(str, 1, max_length-3), '...');

   END IF;
   
   RETURN str_out;

END; //

DELIMITER ;

Then you simply do:

SELECT ELLIPSIS(Message, 10);

Scatterbrain

Best solution to my eyes. If I can suggest (what I think to be) an inprovement: I would change the shortened string to SET str_out = CONCAT(LEFT(str, max_length-3), '...'); The resulting string will not be longer than the "max_length" – Bermudez

Answer 5

Have a look at the MySQL string functions, documented here. You should be able to use some combination of substring and concat to achieve your desired behaviour.

Tade

Answer 6

My approach:

• Let x be the maximum number of characters to display (therefore x + 3 dots will be the longest string displayed)
• You always want LEFT(field,x)
• If LENGTH(field) > x + 3, append 3 dots
• Otherwise if LENGTH(field) > x, append the remainder of field

SELECT CONCAT(
    LEFT(field,x),
    IF(LENGTH(field) > x+3,
       '...',
        IF(LENGTH(field) > x,
            MID(field,x+1,LENGTH(field)),
            ''
        )
    )
) FROM table

Hundley