如何根据空间剪切字符串？

Question

I have this PHP code:

if ((strlen($str) > 120)){
        $str = substr($str, 0, strpos($str, ' ', 110)).' ...';
} 

As you know, my code cuts the string based on the space. I mean it never ends up with cutting a word in the middle.

Anyway, I want to know, can I do the same thing in MySQL?

---

Based on some researches, I figured out there is a MySQL function for such job, something like this:

SELECT SUBSTR(col, 0, 110), other_cols

But as you know, it does't care about the space and might breaks a work.

Answer 1

You could start from something like this and adapt it for your needs:

SELECT CASE WHEN LOCATE(' ' , YOURSTR, 120) > 0 
       THEN SUBSTR(YOURSTR, 1, LOCATE(' ' , YOURSTR, 120)) 
       ELSE YOURSTR END AS YOURSTR
FROM YOUR TABLE;

To answer at what you asked in comment below, try this:

SELECT CASE WHEN LOCATE(' ' , 'abcd efg', 5) > 0 THEN SUBSTR('abcd efg', 1, LOCATE(' ' , 'abcd efg', 5)) ELSE 'abcd efg' END AS STR;
//=> 'abcd'

SELECT SUBSTR('abcdefg', 1, LOCATE(' ' , 'abcdefg', 5)) AS STR;
//=> ''

SELECT SUBSTR('abcd efg', 1, LOCATE(' ' , 'abcd efg', 50)) AS STR;
//=> ''