Replace a string in a blob record with another

Posted on 2005-05-06
Last Modified: 2010-05-18
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.
Question by:jayrod
    LVL 6

    Expert Comment

    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.
    LVL 3

    Author Comment

    Would replace allow me to only search/replace the text I want? So replace ass with butt won't change password to pbuttword?
    LVL 20

    Accepted Solution

    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).
    LVL 6

    Expert Comment


    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

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
    Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now