How to write this kind of SQL in MySQL?

Question

I want to update a column col in table tab,whose data is like follows(comma separated, with the heading comma):

,test,oh,whatever,....,

Which can be too long to display,how can I update the column so that only the first 10 words are left?

Answer 1

Not an answer to your question, but I would recommend to do stuff like this on application level, not in the database.

You are not saying what language you are using. In PHP, this would be a job for the wordwrap function. It is able to intelligently chop off strings at the right position.

Alternatively, is storing the full string in the database, and doing the cutting at output time, not an option as well?

Answer 2

You are looking for substring_index

UPDATE table
SET column = SUBSTRING_INDEX(column, ',', 11)

(do check your UPDATES with SELECT before running them)