• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

Replace a string in a blob record with another

Greetings Experts,

I have records with blob fields, and in them I am trying to replace strings, say: test1 with test2. However, I am afraid I will be overwriting a word like test1111 to look like test11112 with my query:

update nuke_phpbb_posts_text
set post_text = replace(post_text, 'test1', 'test2')
where post_text like '%test1%'

Is there safer way to do this without overwriting parts of the string found?

Thanks for you time in assisting me.
  • 2
1 Solution
Hi jayrod,

Nope, it will not replace test1111 with test11112, it will replace it with test2111.
There aren't so many functions for string manipulation is MySQL, so stick with replace.
another way to replace all occurences in your PHP script - PHP has much more possibilities for text manipulation, and then UPDATE your BLOB field.

German Rumm.
jayrodAuthor Commented:
Would replace allow me to only search/replace the text I want? So replace ass with butt won't change password to pbuttword?
1. easy but possibly insufficient answer: add spaces before and after your word in your criteria for both the search and replace.
2. use a RegEx (I'm not expert for you with regexes).

It will replace password to pbuttword :-) If you only want to replace whole words, use virmaior suggestion #1 - add spaces before and after.

Unfortunately it's not possible to use regexp in MySQL for string manipulation, only for searching...

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now