Professional Web Applications Themes

Escape character in SQL - how do I ignore ? - Coldfusion Database Access

I have laboured through the joys of importing a CSV file and sticking the values in a MySQL database. So far so good. However the CSV file contains a value as follows :- "W\'hampton/Birmingham" As a result of this my SQL falls over. I'm presuming it's because of the \ Can anyone shed any light on whether this is :- a) indeed the case and b) how to rectify this, bearing in mind that the query is generated on the fly for each line in the CSV file. I also have no control over the content of said file. Many ...

  1. #1

    Default Escape character in SQL - how do I ignore ?

    I have laboured through the joys of importing a CSV file and sticking the
    values in a MySQL database. So far so good.
    However the CSV file contains a value as follows :-

    "W\'hampton/Birmingham"

    As a result of this my SQL falls over. I'm presuming it's because of the \
    Can anyone shed any light on whether this is :-

    a) indeed the case and
    b) how to rectify this, bearing in mind that the query is generated on the fly
    for each line in the CSV file. I also have no control over the content of said
    file.

    Many thanks in advance.

    Mark.

    caledoniaman Guest

  2. #2

    Default Re: Escape character in SQL - how do I ignore ?

    OK, this is obviously not one which many people have encountered before. Maybe
    I could approach it from a different angle and see if you can help me.

    In between the <cfquery> tags I am declaring my SQL in a string variable. How
    can I take this string and remove any '\' characters (which will eliminate this
    problem) ?

    Thanks again in advance.

    caledoniaman Guest

  3. #3

    Default Re: Escape character in SQL - how do I ignore ?

    If you're going to be doing this on a regular basis, you'll probably end up
    creating a udf that scrubs the text values, but for now, you could use a simple
    replace() to escape the "\":

    <cfset yourValue = "W\'hampton/Birmingham">
    <cfset newValue = Replace(yourValue, "\", "\\")>

    <cfquery name="test" datasource="#yourDSN#">
    INSERT INTO test (yourColumn)
    VALUES ( '#newValue#' );
    </cfquery>



    mxstu Guest

Similar Threads

  1. Replies: 0
    Last Post: October 9th, 06:09 PM
  2. No escape character with dircect method call in cfquery
    By Lion_b1 in forum Coldfusion Database Access
    Replies: 2
    Last Post: June 30th, 07:57 PM
  3. Escape character when setting variable
    By Joe S. in forum Macromedia ColdFusion
    Replies: 0
    Last Post: February 23rd, 12:30 AM
  4. [PHP] explode and escape character for string separator
    By Brent Baisley in forum PHP Development
    Replies: 3
    Last Post: July 18th, 08:11 PM
  5. explode and escape character for string separator
    By Chris Shiflett in forum PHP Development
    Replies: 7
    Last Post: July 18th, 08:09 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139