MySQL：制作摘录并将其复制到另一列

Question

I need to extract text from a MySQL column and copy that text to another column. This text will be an excerpt of the full text in the first column in that I need to trim the 15 leading characters, and then include only the next 140 +/- characters of the text and copy that text to a new column.

This works, of course, to copy all text from one column to another:

UPDATEtableSET column1=column2

And I see the functions LEFT and RIGHT to trim text from the left or the right.

But is it possible to combine LEFT and RIGHT to strip the first 15 characters and then copy the next + or - 140 characters?

There are no delimiters I can use; all the text will be different. So I need to be able to count characters.

And when I say + or - 140 characters: is it possible to not break a word that may end up at the end of the 140?

Update 11/27/13

This query below by Jungsu Heo works and doesn't break words for a table called wp2_posts, and the columns post_excerpt and post_content:

UPDATE `wp2_posts`
SET post_excerpt = SUBSTRING(SUBSTRING(post_content, 55, 110),  1,
IF(LENGTH(SUBSTRING(post_content, 55, 110)) -
LENGTH(SUBSTRING_INDEX(SUBSTRING(post_content, 55, 110), ' ', -1)) = 0,
LENGTH(SUBSTRING(post_content, 55, 110)), LENGTH(SUBSTRING(post_content, 55, 110)) -
LENGTH(SUBSTRING_INDEX(SUBSTRING(post_content, 55, 110), ' ', -1))));

Answer 1

it is interesting. I have tested what you want.

UPDATED

I have tested. Below query works well even if there is no space. you can test here sqlfiddle. http://www.sqlfiddle.com/#!2/0bf19/2/1. (unfortunately, we can't run DML in sqlfiddle)

UPDATE tab SET cite = SUBSTRING(SUBSTRING(col, 16, 130),  1, IF(LENGTH(SUBSTRING(col, 16, 130)) - LENGTH(SUBSTRING_INDEX(SUBSTRING(col, 16, 130), ' ', -1)) = 0, LENGTH(SUBSTRING(col, 16, 130)), LENGTH(SUBSTRING(col, 16, 130)) - LENGTH(SUBSTRING_INDEX(SUBSTRING(col, 16, 130), ' ', -1))));

This is step by step example:

set @str = 'ABC 1234 56789';

set @ltrim_len = 5;
set @ltrimmed_str = SUBSTRING(@str, @ltrim_len);

set @right_word_warp = SUBSTRING_INDEX(@ltrimmed_str, ' ', -1);

set @final_sub_len = LENGTH(@ltrimmed_str) - LENGTH(@right_word_warp);

SELECT SUBSTRING(@ltrimmed_str,  1, IF(@final_sub_len = 0, LENGTH(@ltrimmed_str), @final_sub_len)) AS cite;
+-------+
| cite  |
+-------+
| 1234  |
+-------+

My original Answer (does not work when there is no space)

'ABC 12345 6789' given, then trim 'ABC' and trim '6789' for exact word warp. Finally we get '12345'.

Here is what I tested. step by step example. final answer is last sql.

set @str = 'ABC 12345 6789';

SELECT @str;
+----------------+
| @str           |
+----------------+
| ABC 12345 6789 |
+----------------+

SELECT SUBSTRING(@str, 5);
+--------------------+
| SUBSTRING(@str, 5) |
+--------------------+
| 12345 6789         |
+--------------------+

SELECT SUBSTRING_INDEX(SUBSTRING(@str, 5), ' ', -1);
+--------------------+
| SUBSTRING(@str, 5) |
+--------------------+
| 12345 6789         |
+--------------------+

SELECT LENGTH(SUBSTRING_INDEX(SUBSTRING(@str, 5), ' ', -1));
+------------------------------------------------------+
| LENGTH(SUBSTRING_INDEX(SUBSTRING(@str, 5), ' ', -1)) |
+------------------------------------------------------+
|                                                    4 |
+------------------------------------------------------+

SELECT SUBSTRING(SUBSTRING(@str, 5),  1, LENGTH(SUBSTRING(@str, 5)) - LENGTH(SUBSTRING_INDEX(SUBSTRING(@str, 5), ' ', -1)) );
+-----------------------------------------------------------------------------------------------------------------------+
| SUBSTRING(SUBSTRING(@str, 5),  1, LENGTH(SUBSTRING(@str, 5)) - LENGTH(SUBSTRING_INDEX(SUBSTRING(@str, 5), ' ', -1)) ) |
+-----------------------------------------------------------------------------------------------------------------------+
| 12345                                                                                                                 |
+-----------------------------------------------------------------------------------------------------------------------+