How can I convert BLOB to text in MySQL?

Question

I have a BLOB field in one of tables and I used the following command to convert it to text:

ALTER TABLE mytable
ADD COLUMN field1_new TEXT;

update mytable set
field1_new = CONVERT(field1 USING utf8);

This did not work and gave me some random characters. Like:

9x

This result is returned as a content of message which does not make sense. I changed the character set to 'latin1'. This one gave me a larger sequence of characters yet still something non-sense. For example:

¢xœ}T]k1|/ô?¬Á/‡ZJpMK“–<$„Ô¥ôqO§»ÑI®¤³¹ß...

Is there anyway to figure out what character set the BLOB field is using so that I can convert it to text properly?

Any help with this problem will be much appreciated. Thanks

Edited: I have to also mention that I used CAST command and it returned:

�x�}T]k1|/�?��/��ZJpMK��<$�ԥ�qO���I������������$:���̬�4�...

I just tried your query and it worked in my test table. Could your data be corrupted? — Tejas, Jan 14 '15 at 11:39
Since another group have done it once I guess the data is not corrupted. — ahajib, Jan 14 '15 at 17:59
Would you like it dumped in HEX? Or maybe it is really TEXT and you are asking to _interpret_ it in some `CHARACTER SET`? — Rick James, Jan 26 '23 at 02:25

score 2 · Answer 1 · edited May 23 '17 at 10:29

2

try using cast:

CAST(field1 AS CHAR(10000) CHARACTER SET utf8)

you can see this post also for more:How do I convert from BLOB to TEXT in MySQL?

edited May 23 '17 at 10:29

Community

1
1

answered Jan 14 '15 at 06:30

Suchit kumar

11,809
3
22
44

How can I convert BLOB to text in MySQL?

1 Answers1