Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2013

    Unanswered: Problems with REPLACE

    I have a strange problem with an export-script, the culprit seems to be the replace command:

    For example:

    mysql -h localhost sourcedbname -uUser -pPass -N -q -e "SET NAMES binary;SELECT SQL_NO_CACHE pmtext.pmtextid AS conversation_id, REPLACE(REPLACE(REPLACE(REPLACE(pmtext.title, \'&lt;\', \'<\'), \'&gt;\', \'>\'), \'&quot;\', \'\\"\'), \'&amp;\', \'&\'), fromuserid AS user_id, fromusername AS username, dateline AS start_date, 0 AS open_invite, 1 AS conversation_open, 0 AS reply_count, GREATEST(2, (SELECT COUNT(*) FROM pm WHERE pm.pmtextid = pmtext.pmtextid)) AS recipient_count, pmtextid AS first_message_id, dateline AS last_message_date, pmtextid AS last_message_id, fromuserid AS last_message_user_id, fromusername AS last_message_username FROM pmtext ORDER BY pmtext.pmtextid" > xf_conversation_master.txt;
    doesn't work and gives me an error:
    Syntax-error at unexpected word `)'
    But when I delete

    REPLACE(REPLACE(REPLACE(REPLACE(pmtext.title, \'&lt;\', \'<\'), \'&gt;\', \'>\'), \'&quot;\', \'\\"\'), \'&amp;\', \'&\'),
    it suddenly works.

    what could the problem be here? Strangely enough it is the same problem with different versions of mysql.

    All rights there have been granted. It doesn't work with:

    PHP Version 5.4.4-14+deb7u3
    mysql Client API version 5.5.31
    pcntl support enabled


    PHP 5.4.16 (cli) (built: Jun 11 2013 16:10:39)
    Client API version 5.1.66

    A little hint would be VERY much appreciated! Thanks!

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    i suspect its to do with the escape character sequence. probably the \'&quot;\', \'\\"\'
    what I'd suggest you do is debug the replace statements one at a time till you find which component isn't working

    as the reported error is "Syntax-error at unexpected word `)'" then it could also be a data error
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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