字符串按长度截短，但不允许切碎单词 [英] String truncate on length, but no chopping up of words allowed

问题描述

我想将MYSQL中的字符串字段长度限制为一定长度，但是我不希望出现任何切碎单词的情况.

I want to limit a string field length in MYSQL on a certain length, but I don't want any chopping up of words to occur.

当我这样做时:

SELECT SUBSTRING('Business Analist met focus op wet- en regelgeving', 1, 28)

我得到这个作为输出:

Business Analist met focus o

但是我想要

Business Analist met focus

如何强制执行28个字符的限制，但又不能切碎单词呢?当然，在[这里选择插入的编程语言] ;-)中很容易，但是我想通过一条简单的语句就知道在MYSQL中是否可行.

How can I enforce a limit of 28 chars, but prevent chopping up words? Off course it's easy in [insert programming language of choice here] ;-), but I want to know if it's possible in MYSQL in a simple statement.

推荐答案

让@str是您的字符串，@len是要剪切的初始位置.然后必要的步骤可能是:

Let @str be your string and @len the initial position to cut at. Then the necessary steps could be:

1. 采用@str的最左侧@len字符.

反转子字符串.

找到反向子串中第一个空格的位置.

Find the position of the first space in the reversed substring.

从该位置减去1.但是，如果找不到空间，请保持位置0.

Subtract 1 from the position. But if no space was found, let the position remain 0.

从@len减去找到的位置并将其命名为cutpos.

Subtract the found position from @len and call it cutpos.

将@str的第一个(最左侧)cutpos字符作为str1，将所有其他字符(从cutpos+1开始)作为str2.

Take the first (leftmost) cutpos characters of @str as str1, take all the other characters (starting from cutpos+1) as str2.

SELECT
  LEFT(str, cutpos) AS str1,
  SUBSTRING(str, cutpos + 1) AS str2
FROM (
  SELECT
    @str AS str,
    @len - IFNULL(NULLIF(LOCATE(' ', REVERSE(LEFT(@str, @len))), 0) - 1, 0) AS cutpos
) s

这篇关于字符串按长度截短，但不允许切碎单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！