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))));
it is interesting. I have tested what you want.
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 |
+-------+
'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 |
+-----------------------------------------------------------------------------------------------------------------------+
Have you looked at the usage of LEFT, RIGHT and SUBSTRING operators within mySQL? You can easily grab characters 16 through whatever.
UPDATE _table_ SET column1 = SUBSTRING(column2,16)
As for breaking words, You may have to use PHP for that.
here's an example sqlFiddle example
Just replace the number 2 with your 15 leading characters, and the number 10 with 140 and it should work. it drops the first 2 characters, then find 10 characters and drop the last chunk by locating a space at the end.
UPDATE `table` SET column1=
LEFT( LEFT(RIGHT(column2,LENGTH(column2)-2),10),
10-
LOCATE(' ',REVERSE(LEFT(RIGHT(column2,LENGTH(column2)-2),10)),1)
);
Alternately you can use SUBSTRING instead of RIGHT like in this sqlFiddle example just replace occurences of 3 with 16(your 15+1) and replace occurences of 10 with 140
UPDATE `table` SET column1=
LEFT( LEFT(SUBSTRING(column2,3),10),
10-
LOCATE(' ',REVERSE(LEFT(SUBSTRING(column2,3),10)),1)
);
edit: actually, since you want a word to end at 140 you might have to replace occurences of 10 with 141 instead since it drops the last chunk of word it finds so if your word ends at 140 you don't want it to drop so try 141.
-------------------------------------------
UPDATED The above answers are wrong it doesn't work for other cases. NEW ANSWER BELOW
this answer has more cases to test around the 15(your 140) boundary
here's the sqlFiddle proof and here's the code:
UPDATE `table` SET column1=
IF(LENGTH(LEFT(SUBSTRING(column2,3),16)) < 16,
LEFT(SUBSTRING(column2,3),16),
LEFT(LEFT(SUBSTRING(column2,3),16),
LENGTH(LEFT(SUBSTRING(column2,3),16))-
LOCATE(' ',REVERSE(LEFT(SUBSTRING(column2,3),16)),1)
)
);
Just change all occurences of 3 to 16(your 15+1) and occurences of 16 to 141(your 140+1).
-------------------------------------------------------------
answer for latest UPDATED QUESTION
UPDATE wp2_posts,
wp2_term_relationships,
wp2_term_taxonomy
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))));
WHERE (wp2_posts.ID = wp2_term_relationships.object_id)
AND (wp2_term_relationships.term_taxonomy_id = wp2_term_taxonomy.term_taxonomy_id
AND wp2_term_taxonomy.taxonomy = 'category'
AND wp2_term_taxonomy.term_id = '213');
or if you prefer to keep all your INNER JOIN syntax
like below
UPDATE wp2_posts
INNER JOIN wp2_term_relationships
ON (wp2_posts.ID = wp2_term_relationships.object_id)
INNER JOIN wp2_term_taxonomy
ON (wp2_term_relationships.term_taxonomy_id = wp2_term_taxonomy.term_taxonomy_id
AND wp2_term_taxonomy.taxonomy = 'category'
AND wp2_term_taxonomy.term_id = '213')
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))));