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.

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

Additionally, when you are using early-returns anyway, there's no reason is: find up to 20 chars followed either by the end-of-line, or a separator. StringJoiner and a Stream wrapper around a single word to allow the more  In some situation, we have to chop some words from a given string but using substring we can't achieve this and loose some characters. Below is the method which can chop the given string. Here I'm demonstrating two methods to help in truncate string with complete words without chopping words. Step 1: Create a console application.

What about splitting on spaces :

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

will return

Business Analist met focus

How to truncate string without breaking a word in half Sometimes you need to shorten a long string to certain length without cutting the final word in half and add the pointer up to the previous space, if the limit finished within a word. Allowed tags: blockquote, a, strong, em, p, u, strike, super, sub, code. All comments are held for moderation. We'll publish all comments that are on topic, not rude, and adhere to our Code of Conduct.You'll even get little stars if you do an extra good job.

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))

Cutting a string to a specified length is accomplished with the substr() function. $string = 'This string is too long and will be cut short. the major problem with this function in that it will take no notice of the words in a string. this thing is rating pretty high on Google's search results; keep up the good work! The first is better. It uses a straightforward logic. The second uses unintuitive, convoluted logic (pad -> substring -> trim). It should be slower for short strings due to the extra padding step.

In SQL it would be...

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

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

Returns the portion of string specified by the start and length parameters. If start denotes the position of this truncation or beyond, FALSE will be returned. use strrevpos function in case your php version does not include it Well this is a script I wrote, what it does is chop up long words with malicious meaning into  Truncate strings with formula. For instance, here I just truncate the strings into six digit from left, and below formula can do a favor. Select a blank cell next to the string you want to truncate, and enter this formula =LEFT(A1,6) (A1 is the string you use, and 6 indicates truncate the string into six characters), then drag fill handle over the cells which also need this formula.

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.

  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.

When FALSE the function does not split the word even if the width is smaller than Returns the given string wrapped at the specified length. on the $width and $​break parameters if they come from untrusted input. up $return[] = trim($string); the preg_replace function in conjunction with the "e" flag (in order to allow the  What is the simplest way to truncate a string to a certain length but keep the words intact? If we use a direct string cut as If (Len(fName) > 24) Then trName = Left(fName, 24) & "" it may cut words in the middle. I need to make the length max 24 characters, but, if it cuts a word, it should also remove this half-word completely. Please, advise.

There's no SUBSTRING function in Excel. Use the MID, LEFT, RIGHT, FIND, LEN, SUBSTITUTE, REPT, TRIM and the MAX function in Excel to extract To extract the leftmost characters from a string, use the LEFT function in Excel. Left Function​. To extract a substring (of any length) before the dash, add the FIND function. C# Truncate String. Implement a truncate method to shorten strings that are too long, but leave others alone. Truncate. This method restricts the maximum length of strings. We truncate a string so that it has a substring of the first specified number of letters. Strings.

The LENGTH function returns the length of a string. TRIM. The TRIM function is used to remove characters from the beginning and end of a string. Here's an  It's not the most readable method, but compared to for instance splitting the string by whitespace and building it up again using a StringBuilder it's much, much faster.

if the plain text is shorter than the maximum length, return the whole text Checks for closing tags, allowing leading and ending space inside the brackets */ if (endOfWordOffset > 6) { // chop the word if it's extra long tag like and the tag was not properly opening and whole site design was messed up. Contact. Simple C# Extension Method to truncate a string, preserving the last word. We needed to truncate a summary down to n number of characters to provide a uniform look and feel. So I whipped up this extension method to take the current string and truncate to n characters, attach an ellipsis to the end, and optionally keep the last word of the string preserved.

Comments
  • You're looking for "word wrap" in MySQL. Sort of.
  • You need logic here. In other words, you need an if statement. If MySQL supports if, then use that, otherwise, no way
  • i think that's quite expensive operation for mysql. just chop the word and get rid of it later in whatever programming language you're using.
  • @keymone Not really, see point 5 (5. Favoring PHP over SQL) at sitepoint.com/mysql-mistakes-php-developers
  • @bicycle there is a difference between implementing AVG in your PHP and doing substring. chances are that if you're doing substring you will need to do other more complex operations in php anyway.
  • charindex is not a valid mysql function. It should be LOCATE instead. This one should be the best answer btw.
  • This worked for me, but I had to change CHARINDEX to LOCATE
  • Was that by any chance you who downvoted my answer some 3 hours ago? If so, I would really appreciate if you elaborated on your reasons. By the way, when you are checking the length at the beginning, you are verifying whether descr is empty and, depending on that, checking the length of either product alone or the two columns delimited with -. But later on, when you are actually finding the position to cut, you are no longer checking if descr is empty or not. That might not result in the most precise cut sometimes, I think.
  • true indeed, good point. I just adjusted it. Yeah i downvoted you because the other answer is easier to implement without too much customisation. It should be adjusted though since charindex is not a valid mysql method while the quesion is about mysql. should be locate instead.
  • But it doesn't cover the case when a space is not found within the specified length. Neither does yours, as a matter of fact. I realise that omitting that case might have made that solution look easier, though. Either way, thanks for explaining your downvote!
  • seems like some poeple don't assume generic inputs... the answer should work for an arbitrary number of words, not just 4! the SUBSTRING_INDEX function is interesting anyway.