MySQL truncate text with ellipsis?

MySQLMySQLi Database

You can truncate the text with ellipsis using LENGTH() with CASE statement. If your length is greater than 7 then truncate the text and add some number otherwise print the number as it is.

To understand the above syntax, let us create a table. The query to create a table is as follows:

mysql> create table TruncateText
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> Number longtext,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.66 sec)

Insert some records in the table using insert command. The query is as follows:

mysql> insert into TruncateText(Number) values('64575868667687');
Query OK, 1 row affected (0.11 sec)
mysql> insert into TruncateText(Number) values('7654332');
Query OK, 1 row affected (0.17 sec)
mysql> insert into TruncateText(Number) values('25434656');
Query OK, 1 row affected (0.19 sec)
mysql> insert into TruncateText(Number) values('6457586');
Query OK, 1 row affected (0.17 sec)
mysql> insert into TruncateText(Number) values('958567686868675757574');
Query OK, 1 row affected (0.13 sec)
mysql> insert into TruncateText(Number) values('374785868968787');
Query OK, 1 row affected (0.21 sec)
mysql> insert into TruncateText(Number) values('58969678685858585858585858585');
Query OK, 1 row affected (0.14 sec)

Display all records from the table using select statement. The query is as follows:

mysql> select *from TruncateText;

The following is the output:

+----+-------------------------------+
| Id | Number                        |
+----+-------------------------------+
|  1 | 64575868667687                |
|  2 | 7654332                       |
|  3 | 25434656                      |
|  4 | 6457586                       |
|  5 | 958567686868675757574         |
|  6 | 374785868968787               |
|  7 | 58969678685858585858585858585 |
+----+-------------------------------+
7 rows in set (0.00 sec)

In the above sample output, we have some numbers whose length is greater than 7. If the length is greater than 7 then we need to append a numeric number after 7th digit. If the length is 7 or equal to 7 then there is no need to append the numeric number.

The query is as follows:

mysql> SELECT *, CASE WHEN LENGTH(Number) > 7
   -> THEN CONCAT(SUBSTRING(Number, 1, 7), '99999999')
   -> ELSE Number END AS AddNumber
   -> FROM TruncateText;

The following is the output:

+----+-------------------------------+-----------------+
| Id | Number                        | AddNumber       |
+----+-------------------------------+-----------------+
|  1 | 64575868667687                | 645758699999999 |
|  2 | 7654332                       | 7654332         |
|  3 | 25434656                      | 254346599999999 |
|  4 | 6457586                       | 6457586         |
|  5 | 958567686868675757574         | 958567699999999 |
|  6 | 374785868968787               | 374785899999999 |
|  7 | 58969678685858585858585858585 | 589696799999999 |
+----+-------------------------------+-----------------+
7 rows in set (0.00 sec)

Look at the above sample output. If the number is greater than 7, then we have truncated all the values from the number and added a number '99999999' after 7th digit.

raja
Updated on 30-Jul-2019 22:30:24

Advertisements