How to truncate text and append ellipsis in MariaDB

Occasionally, you may find that the text returned in a database column is too long. You might just want to return a small chunk of that text followed by an ellipsis or three dots.

In many cases, we can use this, such as the summary of the blog list, the introduction of the article, etc.

In MariaDB, you can do this very easily. A few examples are given below.

Test data

Suppose we have the following notes table with the following rows:

+----+--------------------------------------------+
| id | note                                       |
+----+--------------------------------------------+
|  1 | The quick brown fox jumps over a lazy dog. |
|  2 | Hello World!                               |
+----+--------------------------------------------+

Truncate text

If you want to truncate text to a specified length, you can use the LEFT() function. LEFT() accepts two parameters, the first parameter is the text to be truncated, and the second parameter is the number of reserved characters.

The following statement truncates the note column to 20 characters:

SELECT
  id,
  note,
  LEFT(note, 20) "Truncate"
FROM notes;

Output:

+----+--------------------------------------------+----------------------+
| id | note                                       | Truncate             |
+----+--------------------------------------------+----------------------+
|  1 | The quick brown fox jumps over a lazy dog. | The quick brown fox  |
|  2 | Hello World!                               | Hello World!         |
+----+--------------------------------------------+----------------------+

Dynamic truncation

If the text length exceeds a certain length, the text is truncated and a ellipsis is appended. If the text length does not exceed a certain length, the source text is used. This can be done using IF function conditionals.

The following statement shows that the text is truncated and a dart is appended if the text is longer than 20 characters, otherwise the original text is returned.

SELECT
  id,
  note,
  IF(CHAR_LENGTH(note) > 20,
      CONCAT(LEFT(note, 20),"..."),
      note) "Truncate"
FROM
  notes;

Output:

+----+--------------------------------------------+-------------------------+
| id | note                                       | Truncate                |
+----+--------------------------------------------+-------------------------+
|  1 | The quick brown fox jumps over a lazy dog. | The quick brown fox ... |
|  2 | Hello World!                               | Hello World!            |
+----+--------------------------------------------+-------------------------+

In this example, we use the CHAR_LENGTH() function to get the length of the text, and use the CHAR_LENGTH(note) > 20 expression to check whether the length of the text you is greater than 20, and then use the IF() function to choose between the source text and the truncated text based on the result of the conditional expression. When the length of the text exceeds, use the LEFT() function truncate the text, and use CONCAT() to add a ellipsis to the end of the text.

Conclusion

This article shows a technique for truncating text and appending an ellipsis in MariaDB. We used the IF(), CHAR_LENGTH(), LEFT(), CONCAT() function.