Help with CONCAT function

[jairus]

hello everyone, i'm currently doing this to only allowing a certain amount of words to be displayed and if its more than the allotted it will display (...)

here is the function that i am using:

SELECT *, CONCAT(SUBSTRING_INDEX(title, ' ', 6), ' ') as title FROM $cinematable ORDER BY id DESC LIMIT $start, $entries_per_page

the problem is i have some entries that are less than 5 words and they still have those dots so it looks something like this:

(5 or more words) this entry has five or....

(4 or less words) only four words....

what i'm trying to do is if the entry has less than 5 words i want it to show the entry without the (....) marks.

how woudl i go about doing that?

[tomhath]

You could do it with a CASE, use REGEX to match data with four or more separators. Here's a guess at it to get you started, not tested:

SELECT *,
CASE WHEN title REGEX "(\S+\s+){4}" THEN CONCAT(SUBSTRING_INDEX(title, ' ', 6), ' ')
ELSE title as title
END
FROM $cinematable
ORDER BY id DESC LIMIT $start, $entries_per_page