Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2016
    Posts
    2

    Question Unanswered: escape characters in query result strings?

    I'm a SQL novice and this is perhaps an easy question but my search abilities have failed me. I have a table with text data in it that contains single quotes, slashes, and carriage returns plus line feeds. I'm trying to find a way to run a select statement such that the aforementioned characters are already escaped in the output. Example:

    select * from tableblah
    id story
    1 this can\'t be that hard!\r\nHow do you do it?

    Any ideas? I am completely limited to a select statement's return results - no output files, etc, as this query is being ran through a browser interface which returns the results (vBulletin interface).

    Thanks!

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,794
    Provided Answers: 11
    You can use the replace function, but the select query is going to get very ugly very quickly:

    http://dev.mysql.com/doc/refman/5.7/...nction_replace

  3. #3
    Join Date
    Apr 2016
    Posts
    2
    Thanks for the idea! I can get it to work for a literal value, such as:

    select REPLACE( columnnamehere, '\'', '\\\'' ) from tableblah

    But when I try to use char(13) or char(10), it is not working:

    select REPLACE( columnnamehere, char(13), '\r' ) from tableblah

    Am I doing something wrong in that case? Also, I take it I can stack these, ie:

    select REPLACE( REPLACE( columnnamehere, char(13), '\r' ), '\'', '\\\'' ) from tableblah

    Yes?

Posting Permissions

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