Replace a word in BLOB text by MySQL.

Question

Hello!

I've got a huge mysql table (called tcountriesnew) and a column (called slogen, blob type). In each of those slogen blobs I'd like to replace a word, for example: banana to apple.

Unfortunately I tried to print all the rows with word banana, and it did not work.

select * from tcountriesnew where slogen like '%banana%';

Please help me.

• What i missed, what is the problem with my query?
• How can i replace text in blob?

Answer 1

Which version are you using ? Maybe it's this bug : http://bugs.mysql.com/bug.php?id=27. Otherwise try to cast your blob column.

Answer 2

Depends what you mean by "replace" show in select:

select replace(slogen, 'bananas', 'apples') from tcountriesnew where slogen like '%bananas%';

Or update data in a table:

update tcountriesnew set slogen=replace(slogen, 'bananas', 'apples') where slogen like '%bananas%';

BTW. Why are you using blob for text? You should use text type for textual data and blob for binary data.