#1
  1. Plays with fire
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    942
    Rep Power
    96

    Replace not working


    Hi--

    Just trying to migrate content from one DB to another with a few small changes, but I can't seem to get a simple replace working:

    Code:
    update news set content = replace(content, '<strong>', '<b>');
    MYSQL says zero rows are affected, but I can find rows with the string with this:

    Code:
    SELECT * FROM news WHERE content LIKE '%<strong>%'
    Does Mysql not like the <> characters?
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    REPLACE() is case sensitive, LIKE is not
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. Plays with fire
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    942
    Rep Power
    96
    The "<strong>" string in my fields is lower case. For example:

    "...<strong>My</strong> destiny waits for a sign from <strong>me</strong>."
    I don't see why this wouldn't work.
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by Frank Grimes
    I don't see why this wouldn't work.
    me neither

    dump the table and a few rows, and let me try
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. Plays with fire
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    942
    Rep Power
    96
    Here's the table with a few rows. Some have the string some do not.

    Let me know what you come up with.

    Thanks.
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    worked fine for me

    prior to the update, found 5 rows where content LIKE '%<strong>%'

    after update, found 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. Plays with fire
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    942
    Rep Power
    96
    Well that's not good news.

    What gives? Different version of Mysql? Are you using the same syntax?
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    version 5.5.15, same sql

    i can see the updated rows by running this --
    Code:
    SELECT * FROM news WHERE content LIKE '%</strong>%'
    Last edited by r937; May 20th, 2013 at 03:02 PM.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. Plays with fire
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    942
    Rep Power
    96
    yeah, still not working for me.

    I run this query:

    UPDATE news SET content = REPLACE(content, '<strong>', '<b>');
    and MySQL tells me 0 rows are affected.

    Running this query:

    SELECT * FROM news WHERE content LIKE '%</strong>%'
    I get 107 rows returned.

    This can't be permissions, because I've run other REPLACE queries on the very same table and they've worked fine.
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    have a closer look at the rows which you found using </strong>

    i'll bet they actually say <b>foo</strong>

    that "0 rows affected" from the update might be misleading

    when i ran it, i got 5 rows affected, 0 rows found
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  20. #11
  21. Plays with fire
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    942
    Rep Power
    96
    After digging through this, my sysadmin found the problem. There is a hidden character in after the '<strong> and before the closing '.

    No idea where that came from. I copied and pasted this query from Ultraedit where the character doesn't exist. Only after I paste it in the edit box in phpmyadmin do I notice it.

    I do appreciate all of your help, however. Now, I need to get a pint of Bundy.
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence

IMN logo majestic logo threadwatch logo seochat tools logo