MySQL 字符串长度截断，但不允许截断单词

String truncate on length, but no chopping up of words allowed

提问by Ward Bekker

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.

我想将 MYSQL 中的字符串字段长度限制为某个长度，但我不希望发生任何单词的切碎。

When I do:

当我做：

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

I get this as output:

我得到这个作为输出：

Business Analist met focus o

But I would like

但我愿意

Business Analist met focus

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.

如何强制限制为 28 个字符，但防止切词？当然，在[在此处插入选择的编程语言] 中很容易;-)，但我想知道在 MYSQL 中是否可以用一个简单的语句来实现。

采纳答案by Andriy M

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

让我们@str成为你的字符串和@len切割的初始位置。那么必要的步骤可能是：

1. Take the leftmost @lencharacters of @str.

2. Reverse the substring.

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

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

5. Subtract the found position from @lenand call it cutpos.

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

1. 取 最左边的@len字符@str。

2. 反转子串。

3. 找到反转子串中第一个空格的位置。

4. 1从位置中减去。但如果没有找到空间，让位置保持不变0。

5. 减去找到的位置@len并调用它cutpos。

6. 取as的第一个（最左边）cutpos字符，取所有其他字符（从 开始）作为。@strstr1cutpos+1str2

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

回答by JeffProd

What about splitting on spaces :

在空格上拆分怎么样：

SELECT SUBSTRING_INDEX('Business Analist met focus op wet- en regelgeving',' ',4)

will return

将返回

Business Analist met focus

回答by Narnian

Very interesting problem. Here's how I did it:

很有趣的问题。这是我如何做到的：

//gets initial string - use 29 instead of 28 to see if the 29th  character is a space
SELECT SUBSTRING('Business Analist met focus op wet- en regelgeving', 1, 29) 

//inverts the string, so we can get the first 
SELECT REVERSE( SUBSTRING('Business Analist met focus op wet- en regelgeving', 1, 29))

// find the charindex of the first space (last space in the string not reversed)
SELECT CHARINDEX(' ', REVERSE( SUBSTRING('Business Analist met focus op wet- en regelgeving', 1, 29)))

// get the substring from the first (last) space
SELECT  SUBSTRING(REVERSE( SUBSTRING('Business Analist met focus op wet- en regelgeving', 1, 29)), CHARINDEX(' ', REVERSE( SUBSTRING('Business Analist met focus op wet- en regelgeving', 1, 29))), 29)

// reverse the string again to unfold it.
SELECT REVERSE(SUBSTRING(REVERSE( SUBSTRING('Business Analist met focus op wet- en regelgeving', 1, 29)), CHARINDEX(' ', REVERSE( SUBSTRING('Business Analist met focus op wet- en regelgeving', 1, 29))), 29))


// to try different lengths...
DECLARE  @size  int
select @size = 24
SELECT REVERSE(SUBSTRING(REVERSE( SUBSTRING('Business Analist met focus op wet- en regelgeving', 1, @size)), 
CHARINDEX(' ', REVERSE( SUBSTRING('Business Analist met focus op wet- en regelgeving', 1, @size))), @size))

回答by László Gácsfalvy

Simple function:

简单的功能：

DROP FUNCTION IF EXISTS fn_maxlen;
delimiter //
CREATE FUNCTION fn_maxlen(s TEXT, maxlen INT) RETURNS VARCHAR(255)
BEGIN

 RETURN LEFT(s, maxlen - LOCATE(' ', REVERSE(LEFT(s, maxlen))));

END//
delimiter ;

Use:

用：

SELECT fn_maxlen('Business Analist met focus op wet- en regelgeving', 28);

回答by bicycle

Build on Narnian's answer, here's one that works with two fields (a.product,a.descr) and where "..." is added when the string is truncated. a.descr can be empty as well.

基于纳尼安的答案，这里有一个适用于两个字段 (a.product,a.descr) 并且在字符串被截断时添加 "..." 的地方。a.descr 也可以为空。

  IF (
CHARACTER_LENGTH(
  IF(
    a.descr = '',
    a.product,
    CONCAT_WS(' - ',a.product,a.descr)
  )
)>35,
IF(
  a.descr = '',
  CONCAT(
    REVERSE(SUBSTRING(REVERSE( SUBSTRING(a.product, 1, 35)), locate(' ', REVERSE( SUBSTRING(a.product, 1, 35))), 35)),
    '...'
  ),
  CONCAT(
    REVERSE(SUBSTRING(REVERSE( SUBSTRING(CONCAT_WS(' - ',a.product,a.descr), 1, 35)), locate(' ', REVERSE( SUBSTRING(CONCAT_WS(' - ',a.product,a.descr), 1, 35))), 35)),
    '...'
  )
),
CONCAT_WS(' - ',a.product,a.descr)
)

I needed something like this so that's why i added it. Might help someone else.

我需要这样的东西，所以这就是我添加它的原因。可能会帮助别人。

回答by Federico Peretti

@Andriy M. I liked very much your answer :) Anyway I find on my db it works better if you change lines 2 and 3 like this:

@Andriy M. 我非常喜欢你的回答 :) 无论如何，我发现在我的数据库上，如果你像这样更改第 2 行和第 3 行，效果会更好：

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

Don't know if it's my fault or what, but the other way it sometimes truncated strings on the first letter when their length was <@len, i.e. "First s" - "tring" instead of "First" "string" when @len=13

不知道这是我的错还是什么，但另一方面，当字符串的长度为 <@len，即“First s”-“tring”而不是“First”“string”时，它有时会在第一个字母上截断字符串@长度=13

I post you a working example:

我给你贴了一个工作示例：

CREATE TABLE `test` (
  `sometext` varchar(65)
);

INSERT INTO `test` (`sometext`) VALUES
('Firs strin'),
('Alll right'),
('third string'),
('fourth string'),
('a longer example string'),
('Supercalifragilisticexpialidocious');

SELECT
  IF(LENGTH(str)<=12,str,LEFT(str, cutpos)) AS str1,
  IF(LENGTH(str)<=12,'',SUBSTRING(str, cutpos + 1)) AS str2
FROM (
  SELECT
    sometext AS str,
    12 - IFNULL(NULLIF(LOCATE(' ', REVERSE(LEFT(sometext, 12))), 0) - 1, 0) AS cutpos
  FROM test
) s

And here is a not-workingexample using your original code:

这是使用原始代码的无效示例：

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

I'm not sure if it's a utf8 issue, or I just misinterpreted your code, or what else...

我不确定这是否是 utf8 问题，或者我只是误解了您的代码，或者还有什么...

回答by tkerwood

In SQL it would be...

在 SQL 中，它将是...

select Substring('Business Analist met focus op wet- en regelgeving', 0 , 28 + 2 - CharIndex(' ',  REVERSE(SUBSTRING('Business Analist met focus op wet- en regelgeving', 0, 28 + 1 )),0))

I dont know if all these functions are available in MYSQL

不知道MYSQL有没有这些功能

EDIT: I think for MYSQL substitute "Locate" for "CharIndex"

编辑：我认为 MYSQL 用“定位”代替“CharIndex”

回答by Jason

Seems like people don't read the mysql manual:

似乎人们不阅读mysql手册：

Original: SELECT SUBSTRING('Business Analist met focus op wet- en regelgeving', 1, 28)gives broken words.

原文：SELECT SUBSTRING('Business Analist met focus op wet- en regelgeving', 1, 28)断言。

Modified: SELECT SUBSTRING_INDEX('Business Analist met focus op wet- en regelgeving', ' ' , 4)gives unbroken words

修改：SELECT SUBSTRING_INDEX('Business Analist met focus op wet- en regelgeving', ' ' , 4)给出完整的单词

SUBSTRING_INDEX(string, delimiter, number)will truncate a string by the number of times delimiter is found. Make your delimiter a space and you will get whole words only. so:

SUBSTRING_INDEX(string, delimiter, number)将根据找到分隔符的次数截断字符串。让你的分隔符成为一个空格，你只会得到整个单词。所以：

SUBSTRING_INDEX( LEFT('Business Analist met focus op wet- en regelgeving',28), ' ' , 4)should do it.

SUBSTRING_INDEX( LEFT('Business Analist met focus op wet- en regelgeving',28), ' ' , 4)应该这样做。