Menu
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.
519
If it's longer than a given length n , clip it to length n ( substr or slice ) and add html entity … (…) 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 ?
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.
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.
What about splitting on spaces :
SELECT SUBSTRING_INDEX('Business Analist met focus op wet- en regelgeving',' ',4)
will return
Business Analist met focus
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
