Geek Answers Handbook

Replace Word in BLOB Text with MySQL

I have a huge mysql table (called tcountriesnew ) and a column (called slogen, blob type ). In each of these slogen blobs, I would like to replace a word, for example: banana with apple .

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

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

Please help me.

  • What did I miss, what is the problem with my request?
  • How can I replace text in blob?
+7
mysql select replace blob
source share
3 answers

Depends on what you mean by "replacement" in the choice:

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

Or update the data in the table:

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

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

+7
source share

In some cases, it is necessary to save BLOB texts. In my case, for some reason, the Drupal 7 developers decided to use blob for all TEXT columns - this is because of control over the developers.

To convert blob to text, use the MySql conversion function. Then you have to save it in the database and convert it again to blob - but it is automatically processed by MySQL. Thus, the following query will do the trick:

 UPDATE tcountriesnew SET slogen = replace(CONVERT(slogen USING utf8), 'bananas', 'apples') WHERE slogen LIKE '%bananas%';

In MySQL 5.5, this completely resolved my problem.

Also configure PhpMyAdmin to display blob data

+1
source share

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

0
source share

More articles:


All Articles