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

user_127401 0
Viewed 25882

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.

substring mysql sql
8 Answers

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);
user_71880970
Related