How to Truncate Text with an Ellipsis in MariaDB

Sometimes you might find that the amount of text returned in a database column is too long. You might just want to return a short snippet of that text, followed by an ellipsis or three dots.

Fortunately, this is relatively easy to do in MariaDB.

Three Periods

Here’s an example of appending three periods (instead of an ellipsis character) to a column whenever the number of characters in that column exceed a certain length:

SELECT 
    IF(CHAR_LENGTH(ProductDescription) > 32, 
        CONCAT(LEFT(ProductDescription, 32),"..."), 
        ProductDescription) AS "Short Desc",
    ProductDescription AS "Full Desc"
FROM Products;

Result:

+-------------------------------------+-----------------------------------------+
| Short Desc                          | Full Desc                               |
+-------------------------------------+-----------------------------------------+
| Purple. Includes left handed car... | Purple. Includes left handed carry box. |
| Blue. Includes right handed carr... | Blue. Includes right handed carry box.  |
| Approximate 45 minute waiting pe... | Approximate 45 minute waiting period.   |
| Approximate 30 minute waiting pe... | Approximate 30 minute waiting period.   |
| Wooden handle. Free wine glasses... | Wooden handle. Free wine glasses.       |
| Orange. Includes spare fingers.     | Orange. Includes spare fingers.         |
| Tied with vines. Very chewable.     | Tied with vines. Very chewable.         |
| Brown ceramic with solid handle.    | Brown ceramic with solid handle.        |
+-------------------------------------+-----------------------------------------+

In this case we use the CHAR_LENGTH() function inside an IF() function to determine whether or not the string is long enough to warrant shortening it. We then use the LEFT() function inside the CONCAT() function to append a few dots to the short description.

Using an Actual Ellipsis Character

And here it is again, but with an actual ellipsis character instead of three dots:

SELECT 
    IF(CHAR_LENGTH(ProductDescription) > 32, 
        CONCAT(LEFT(ProductDescription, 32),"…"), 
        ProductDescription) AS "Short Desc",
    ProductDescription AS "Full Desc"
FROM Products;

Result:

+-------------------------------------+-----------------------------------------+
| Short Desc                          | Full Desc                               |
+-------------------------------------+-----------------------------------------+
| Purple. Includes left handed car…   | Purple. Includes left handed carry box. |
| Blue. Includes right handed carr…   | Blue. Includes right handed carry box.  |
| Approximate 45 minute waiting pe…   | Approximate 45 minute waiting period.   |
| Approximate 30 minute waiting pe…   | Approximate 30 minute waiting period.   |
| Wooden handle. Free wine glasses…   | Wooden handle. Free wine glasses.       |
| Orange. Includes spare fingers.     | Orange. Includes spare fingers.         |
| Tied with vines. Very chewable.     | Tied with vines. Very chewable.         |
| Brown ceramic with solid handle.    | Brown ceramic with solid handle.        |
+-------------------------------------+-----------------------------------------+

The ellipsis uses less space. This is because it’s a single character, as opposed to the three dots (which are three separate characters).

Truncate ALL Rows, Regardless of Length

If you need to truncate all rows, regardless of their length, then you don’t need to include the IF() function.

In this case, the code can be shortened to something like this:

SELECT 
    CONCAT(LEFT(ProductDescription, 15), '...') AS "Short Desc",
    ProductDescription AS "Full Desc"
FROM Products;

Result:

+--------------------+-----------------------------------------+
| Short Desc         | Full Desc                               |
+--------------------+-----------------------------------------+
| Purple. Include... | Purple. Includes left handed carry box. |
| Blue. Includes ... | Blue. Includes right handed carry box.  |
| Approximate 45 ... | Approximate 45 minute waiting period.   |
| Approximate 30 ... | Approximate 30 minute waiting period.   |
| Wooden handle. ... | Wooden handle. Free wine glasses.       |
| Orange. Include... | Orange. Includes spare fingers.         |
| Tied with vines... | Tied with vines. Very chewable.         |
| Brown ceramic w... | Brown ceramic with solid handle.        |
+--------------------+-----------------------------------------+

Omit the Ellipsis

And if you don’t even need the ellipsis/three periods, then you can even shorten it further, to something like this:

SELECT 
    LEFT(ProductDescription, 15) AS "Short Desc",
    ProductDescription AS "Full Desc"
FROM Products;

Result:

+-----------------+-----------------------------------------+
| Short Desc      | Full Desc                               |
+-----------------+-----------------------------------------+
| Purple. Include | Purple. Includes left handed carry box. |
| Blue. Includes  | Blue. Includes right handed carry box.  |
| Approximate 45  | Approximate 45 minute waiting period.   |
| Approximate 30  | Approximate 30 minute waiting period.   |
| Wooden handle.  | Wooden handle. Free wine glasses.       |
| Orange. Include | Orange. Includes spare fingers.         |
| Tied with vines | Tied with vines. Very chewable.         |
| Brown ceramic w | Brown ceramic with solid handle.        |
+-----------------+-----------------------------------------+