SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    SitePoint Zealot HenriIV's Avatar
    Join Date
    Jun 2004
    Location
    France
    Posts
    129
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Replace text in a table

    Hi,

    I'm trying to do a search & replace in each table. Is it possible?
    Ie replace every occurence of President X by President Y whatever the field

    I've found the REGEXP function but have no idea about using it with an UPDATE instruction

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    one way to do it is to script/export the database using something like the export feature of phpmyadmin

    this will generate a script containing CREATE TABLE statements, followed by INSERT statements for all the rows

    just open the sql file in a text editor, do a search/replace, then upload it to the database again
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    you can't use MySQL-REGEXP feature for variable replacements because it doesn't create backreferences.

    If you just want to replace "President X" by "President Y" in different columns then you can do it with subsequent UPDATEs:
    UPDATE table SET col1 = 'President Y' WHERE col1 = 'President X';
    UPDATE table SET col2 = 'President Y' WHERE col2 = 'President X';
    and so on

    If the content is variable you can use the script language of your choice. Just fetch the matching rows (for this purpose you could use the MySQL-REGEXP) and use its regexp-engine to create the UPDATEs.

    Or do it by ex-/import, as Rudy suggested.
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  4. #4
    SitePoint Zealot HenriIV's Avatar
    Join Date
    Jun 2004
    Location
    France
    Posts
    129
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks kleineme and r937, that's what I did using php but it's not easy with large databases so I thought it may be possible directly


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •