Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2006
    Posts
    9

    Unanswered: Escape Character Not Working

    Code:
    INSERT INTO `textpattern` VALUES (18,'2006-04-23 22:18:00','zarathu','2006-10-22 23:10:27','zarathu','Rednecks','','That\'s right.\r\n\r\nThese filthy rednecks think they\'re having the time of their lives while . . . .
    Look closer.

    Code:
    That\'s
    Code:
    they\'re
    For a rather sickening reason that is still unknown to me, those escape characters to show that there is a single quote there is specifically NOT working the way it should. Therefore, it screws up the entire statement.

    Help.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Not 100% sure about MySQL but the ANSI standard for embedding single quotes in a character literal is two single quotes:
    ... VALUES ( 'That''s working' )

  3. #3
    Join Date
    Nov 2006
    Posts
    9
    Quote Originally Posted by shammat
    Not 100% sure about MySQL but the ANSI standard for embedding single quotes in a character literal is two single quotes:
    ... VALUES ( 'That''s working' )
    Hmm... Can anybody else confirm this?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i can confirm that that is the standard sql way of escaping the single quote embedded in a string (delimited by single quotes)

    i can also confirm that mysql supports it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2006
    Posts
    9
    Quote Originally Posted by r937
    i can confirm that that is the standard sql way of escaping the single quote embedded in a string (delimited by single quotes)

    i can also confirm that mysql supports it
    Alright, so I should replace all:

    \'

    with:

    ''

    ^ The above is two single-quotes.

    I'll update if I have any problems.

  6. #6
    Join Date
    Nov 2006
    Posts
    9
    Another question--does this apply for double-quotes? The SQL exporter used \" for them, but when I removed the \ character, nothing was affected.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    double quotes are used only within strings...
    Code:
    insert into comments (comment) 
    values ( '"Oh, no," said Todd. "That''s what she said."' )
    double quotes are also the standard sql quoted identifier delimiter ...
    Code:
    select count(*) as "Total Rows" from ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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