Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 519
Ward Bekker Avatar asked Jul 11 '11 13:07

Ward Bekker


People also ask

How do you trim a long string?

If it's longer than a given length n , clip it to length n ( substr or slice ) and add html entity &hellip; (…) to the clipped string. function truncate( str, n, useWordBoundary ){ if (str. length <= n) { return str; } const subString = str. slice(0, n-1); // the original check return (useWordBoundary ?

How do you truncate a string in SQL?

SQL Server TRIM() Function The TRIM() function removes the space character OR other specified characters from the start or end of a string. By default, the TRIM() function removes leading and trailing spaces from a string. Note: Also look at the LTRIM() and RTRIM() functions.

How do you short a string in JavaScript?

The slice() method extracts a part of a string. The slice() method returns the extracted part in a new string. The slice() method does not change the original string.


2 Answers

What about splitting on spaces :

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

will return

Business Analist met focus
like image 149
JeffProd Avatar answered Oct 17 '22 15:10

JeffProd


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

  1. Take the leftmost @len characters of @str.

  2. Reverse the substring.

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

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

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

  6. 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
like image 10
Andriy M Avatar answered Oct 17 '22 16:10

Andriy M