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

Asked 2011-07-11T13:13:31.087

20

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.

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.

mysql sql substring

Edit Report

1 Answer

10

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

Take the leftmost @len characters of @str.
Reverse the substring.
Find the position of the first space in the reversed substring.
Subtract 1 from the position. But if no space was found, let the position remain 0.
Subtract the found position from @len and call it cutpos.
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

answered 2011-07-11T17:02:40.377

Your Answer