SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: mysql command

  1. #1
    SitePoint Zealot honging's Avatar
    Join Date
    Jan 2001
    Location
    Chapel Hill, NC
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql command

    is there a mysql command that will change a certain number of characters to something else?

    the problem is someone scripted a lyrics script, except it takes each quote and makes it multiple quotes. i don't even know ow to fix it, but i want to at least update the database so it doesn't do '' instead of '

    how would i go about changing all the '' to ' ?

    thanks!

  2. #2
    SitePoint Zealot
    Join Date
    Feb 2002
    Location
    UK
    Posts
    146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i assume this was done to counter the problem of inserting data that contains single quotes into a database.

    see http://www.4guysfromrolla.com/webtech/051899-1.shtml for an example using asp.

    one option would be to just convert double quotes back to single quotes after retrieving the info from the db.

    Alternativley this might be useful for you.

    the string replace function:
    http://www.mysql.com/doc/S/t/String_functions.html

    you should be able to use it like this:
    UPDATE table SET field = REPLACE(field, '\"', '\'');

    this will replace *all* instances of " with ' for that field.

    best check it on a test database first though

  3. #3
    SitePoint Zealot honging's Avatar
    Join Date
    Jan 2001
    Location
    Chapel Hill, NC
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    there seemed to be an error with that... i couldn't get it to work...

    are the three apostrophes at the end supposed to be there?

  4. #4
    SitePoint Zealot
    Join Date
    Feb 2002
    Location
    UK
    Posts
    146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry, that should have been.
    UPDATE table SET field = REPLACE(field, '\"', '\'');

    or in case this post messes up again

    UPDATE table SET field = REPLACE(field, '\(double quotes)', '\(single quotes)');

    you would replace (double quotes) with " and (single quotes) with '


    those darn escaping chars again

    hope this is clear and hope it works this time

  5. #5
    SitePoint Zealot honging's Avatar
    Join Date
    Jan 2001
    Location
    Chapel Hill, NC
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    argh, when i tried to do

    update lyrics set lyrics = replace(lyrics,'\(double apostrophes)','\(single quote)');

    it gave me an error saying that \(double quotes)' is not a known command... SO i tried changing all the single quotes to semicolons then tried changing two semicolons (since '' would become ; back into single quotes but now they've simply dissappeard from my database all together

  6. #6
    Happy Holidays !! Paul S's Avatar
    Join Date
    Mar 2001
    Location
    Mexico
    Posts
    1,287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by honging
    argh, when i tried to do

    update lyrics set lyrics = replace(lyrics,'\(double apostrophes)','\(single quote)');

    it gave me an error saying that \(double quotes)' is not a known command... SO i tried changing all the single quotes to semicolons then tried changing two semicolons (since '' would become ; back into single quotes but now they've simply dissappeard from my database all together
    update lyrics set lyrics = replace(lyrics,'"',"'")

    the first one : single quote + double quote + single quote,
    the second one : double quote + single quote + double quote.

    That way you don't need to escape the quotes.
    Now in php that should be written as

    $sql = 'update lyrics set lyrics = replace(lyrics,\\'"\\',"\\'")';

    Paul

  7. #7
    SitePoint Zealot
    Join Date
    Feb 2002
    Location
    UK
    Posts
    146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry, i obviously wasn't clear enough in my second post and confused the matter

    i only used the (double quotes) (single quotes) stuff as one of the \ chars disappeared from my first post.

    looks like Paul has sorted it out tho' with his clearer answer


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
  •