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.
Who is Participating?
virmaiorConnect With a Mentor Commented:
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).
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?

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...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.