+ Start a Discussion

Possible to escape special characters in SOQL??

I'm having problems with a SOQL statement in one of my ajax pages and from what I can tell in the docs, it's not possible to do a "like" search and escape special characters... True?

Basically, I have a soql statement that takes in a value entered by the user.  If the value contains a single quote, the soql breaks.  I've tried escaping with ( \ ) I've tried replacing the singel quote with two single quotes ala SQL and I've tried replacing the single quote with ' and none of it works.

Is there a solution to this or should I just cut off the stuff after the quote and search on that?


You should be able to escape ' with a \
My bad... I wasn't escaping the escape character in my replace...

        var city = document.getElementById("city").value;
        if (document.getElementById("city").value.indexOf("'") != -1) {
            //alert("there's a '");
            city = city.replace(/'/g,"\\'");
Ron HessRon Hess
here's a one liner that catches most of the chars that need escaping in SOSL

    who = who.replace(/([\'\&\|\!\(\)\{\}\[\]\^~\:\\\+\-])/g, "\\$1");

Message Edited by Ron Hess on 05-23-2006 11:03 PM

I believe your solution should work for me but I have tried several workarounds but nothing seems to work. I modified your .replace sequence to include the " character.
I have a merge field that has virtually every special character ", ', /, \, @, etc. and am using the following code:
<html> <head>
<script type="text/javascript" src="/js/functions.js"> </script>
var who = "{!Lead.Product_Description__c}".replace(/([\"\'\&\|\!\(\)\{\}\[\]\^~\:\\\+\-])/g, "\\$1");
I have even tried to assign the merge field to a variable and it fails as well.  It appears the " causes the interpreter to detect the end of expression and results an error.
I also tried to make it a RegExp expression with similar results because the field also has a / in the string.
This is the source when the script fails: Line 8, Char 15, Error: ';' expected
var who = "2" Square/Diamond Paper Sticker  - Other sizing??".replace(/([\"\'\&\|\!\(\)\{\}\[\]\^~\:\\\+\-])/g, "\\$1");

There could be a way to escape the " character from a merge field, but I've also not been able to figure out how.
It's a waste of an API call, but this is what I've been doing when retrieving values from a text area field as a short term workaround. In my case, I have an S-control that adds a contact to an existing event. If the description field of the event is not empty, then I do a query to get the description of that event and then I'm able to replace the special characters (including the ")properly by using the replace() function.
Thanks. The query works great, but as you said, a wasted call.
Three things here:

1) Not completely Salesforce's fault that it breaks things, per se; JavaScript doesn't have a multi-line quote feature like Perl does. If you install a Perl scripting language in your browser and use that in a tag, you'll find that multi-line quotes are simple and efficient to get things working.

2) You can, however, use SUBSTITUTE() in a formula for fields with known problems to replace carriage returns and double-quotes with escaped versions and query the result of the formula instead.

3) Don't query if you can retrieve; retrieve gets you the information you want, but is faster than a query (strictly uses the index, and is presumably optimized for this very type of purpose).
the common fix for this that i've seen is to put the merge field into a div that's hidden, then access the value from javascript via the div, rather than trying to inject the value directly into the javascript.

BTW, a query call with a just a where id='foo' clause performs the same as a retrieve call (they both build the internal query representation and execute it).

I am not able to use the following code to escape quotation charater ".



var="{!Case.Reference_Type__c}".replace(/([\'\"\!\@\#\$\%\^\&\~\*\(\)])/g, "$1");



plz help!!