MySQL truncate text with ellipsis

user_795382 0
Viewed 12686

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!

trim ellipsis mysql text
6 Answers

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);
user_10394880

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
user_60370370
Related