+ Post New Thread
Results 1 to 2 of 2

Thread: Commands to search and replace in mysql table

  1. #1
    Fli
    Fli is online now
    Administrator Fli's Avatar
    Join Date
    Mar 2013
    Posts
    2,464

    Commands to search and replace in mysql table



    Hi, sometimes we need to replace some string in mysql by another string. What command to use?

    You may try this command to Preview matching columns:
    Code:
    SELECT REPLACE(mycolumn, 'oldstring', 'newstring') FROM tablename
    After preview, you can do search and replace in mysql (canot be reverted, do full mysql backup first):
    Code:
    UPDATE REPLACE(mycolumn, 'oldstring', 'newstring') FROM tablename
    When you need to replace partially (part of the word, or part of the string, then you can use LIKE %. The source of this info is a thread how to bulk delete/replace in overspammed PHPBB forum mysql.
    Code:
    update TableName set ColumnName = REPLACE(ColumnName, "']", '"]') WHERE ColumnName LIKE "%']%";
    (Again this will permanently delete in your mysql, so backup it first)

    How to delete all entries which contain partial string?
    Code:
    DELETE FROM `yourmysqltable` WHERE (`yourcolumnname` LIKE '%partialstring%') LIMIT 20000;
    (Again this will permanently delete in your mysql, so backup it first)

  2. #2
    Fli
    Fli is online now
    Administrator Fli's Avatar
    Join Date
    Mar 2013
    Posts
    2,464


    Is this useful / helpfull? Yes | No
    "']" - means im trying to replace ']
    "%']%" - means im trying to find it "in phrasse" like: phrasse']phrasse

+ Post New Thread

Similar Threads

  1. Replies: 0
    Last Post: 08-31-2017, 02:11 PM
  2. How to search and replace in Linux files
    By Fli in forum Programming, scripting
    Replies: 0
    Last Post: 09-09-2016, 10:37 AM
  3. Replies: 0
    Last Post: 08-05-2016, 10:56 AM
  4. How to make mysql query search case insensitive?
    By Fli in forum PHP, MySQL Forum
    Replies: 0
    Last Post: 09-22-2013, 04:08 PM
  5. Replies: 1
    Last Post: 08-08-2013, 12:16 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
 Protected by : ZB BLOCK  &  StopForumSpam