1

i need to convert one field of mysql from text to varchar(1000) for preventing it create tmp table on disk. but there are many records has over 1000 in length. the field name hometext. in table have other field name bodytext ( text type)

Now i thinking of move the end part of string to the other field name "bodytext" before convert it. read field hometext and cut from char number 999 to the end in to 2 part A, B read field bodytext in to part C

edit the table in put it back part A to hometext and add part B + C put in to bodytext

i dont have much knowlege about php and mysql so could anyone help

thanks very much

=Sample_Table before

field_hometext:

1aaaaa bbbbbbbdd

2aaa bbbbbbb

field_bodytext:

1ccccc

2cccc

====================================================================

= Sample_Table affter cut and move all chars longer than 8 char of all records from field_hometext to field_bodytext

field_hometext:

1aaaaa b

2aaa bbb

field_bodytext:

bbbbbbdd 1ccccc

bbbb 2cccc

thanks for any help

Binh Nguyen
  • 1,313
  • 3
  • 17
  • 27

1 Answers1

1

You can use SUBSTRING to extract required no. of characters for field_hometext.

CONCAT the set of extracted characters to the field field_bodytext.

// Return 1st 8 characters.
SUBSTRING('1aaaaa bbbbbbbdd', 1, 8) AS field_hometext // Output - 1aaaaa b

// Return substring starting from 9th character till the end 
// and prepend it to the beginning of field_bodytext
CONCAT(SUBSTRING('1aaaaa bbbbbbbdd', 9), ' ', '1ccccc') AS field_bodytext // Output - bbbbbbdd 1ccccc

Your final update query will look like this,

UPDATE sample_table SET
field_bodytext = CONCAT(SUBSTRING(field_hometext, 1000), ' ', field_bodytext),
field_hometext = SUBSTRING(field_hometext, 1, 999)
WHERE CHAR_LENGTH(field_hometext) >= 1000;
Samir Selia
  • 7,007
  • 2
  • 11
  • 30
  • thank for great help.. I just test it and corect some thing as the char_length vs length ========================== UPDATE sample_table SET field_bodytext= CONCAT(SUBSTRING(field_hometext, 1000), ' ', field_bodytext), field_hometext= SUBSTRING(field_hometext, 1, 999) WHERE CHAR_LENGTH(field_hometext) >= 1000; – Binh Nguyen Nov 27 '18 at 09:50
  • LENGTH function gives out the count of bytes. You need to use CHAR_LENGTH function instead – Madhur Bhaiya Nov 27 '18 at 09:57
  • Oh yes. Just noticed, Updated my answer. Thanks guys :) – Samir Selia Nov 27 '18 at 10:05
  • anyway to cut at space before char 999 will be great help!! ? – Binh Nguyen Nov 27 '18 at 10:33
  • @BinhNguyen, yes you can cut it at a space. Here's the reference link https://stackoverflow.com/questions/12775352/last-index-of-a-given-substring-in-mysql – Samir Selia Nov 27 '18 at 11:50