MySQL用省略号截断文本 [英] MySQL truncate text with ellipsis

问题描述

假设我有一列MySQL表:消息".它是TEXT类型. 我现在想查询所有行，但是文本可以很大(不是很大，但是很大)，我只想获取它们的摘要.例如，结果可以填充到列表中.

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.

是否可以将文本修剪为特定的长度(例如10个字符)，如果文本被修剪，则添加省略号?

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

例如:

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

查询结果:

1234567...
1234567890
12345
1234567...

谢谢！

推荐答案

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

测试/确认:

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

这篇关于MySQL用省略号截断文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！