Escape character in SQL - how do I ignore ?

[caledoniaman]

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]

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.

[mxstu]

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>