LIKE or CONTAINS in SOQL Query

Question

I'm trying obtain some data from the following URL, using a JavaScript code:

http://data.cityofnewyork.us/resource/erm2-nwe9.json

That's how I construct my query:

//data URL variables
var start_date = '2013-08-01'; //YYYY-MM-DD
var end_date = '2013-08-08';   //YYYY-MM-DD
var c_type = 'Noise';          // Complaint Type

// Build the data URL
var URL = "http://data.cityofnewyork.us/resource/erm2-nwe9.json"; // API Access Endpoint
URL += "?";                                                   
URL += "$where=";                                             
URL += "(latitude IS NOT NULL)";                              
URL += " AND ";
URL += "(complaint_type='" + c_type + "')";                    
URL += " AND ";
URL += "(created_date>='" + start_date + "') AND (created_date<='" + end_date + "')"; 
URL += "&$group=complaint_type,descriptor,latitude,longitude";                        
URL += "&$select=descriptor,latitude,longitude,complaint_type";                       
URL = encodeURI(URL);

And how I'm testing it so far:

$.getJSON(URL, function(data)
{
        console.log(data)
});

Right now it works fine, but I should consider any complaint type that contains a single world ("Noise"):

URL += "(complaint_type LIKE '%" + c_type + "%')";

Encoded URL (seems OK):

http://data.cityofnewyork.us/resource/erm2-nwe9.json?$where=(latitude%20IS%20NOT%20NULL)%20AND%20(complaint_type%20LIKE%20'%25Noise%25')%20AND%20(created_date%3E='2013-08-01')%20AND%20(created_date%3C='2013-08-08')&$group=complaint_type,descriptor,latitude,longitude&$select=descriptor,latitude,longitude,complaint_type

Error:

{
  "code" : "query.compiler.malformed",
  "error" : true,
  "message" : "Error, could not parse SoQL query \"select descriptor,latitude,longitude,complaint_type from #erm2-nwe9 where (latitude IS NOT NULL) AND (complaint_type LIKE '%Noise%') AND (created_date>='2013-08-01') AND (created_date<='2013-08-08') group by complaint_type,descriptor,latitude,longitude\"",
  "data" : {
    "query" : "select descriptor,latitude,longitude,complaint_type from #erm2-nwe9 where (latitude IS NOT NULL) AND (complaint_type LIKE '%Noise%') AND (created_date>='2013-08-01') AND (created_date<='2013-08-08') group by complaint_type,descriptor,latitude,longitude"
  }
}

The documentation seems that it is possible to use LIKE, but I can't get it to work.

I don't know how to do this.

I am not familiar with SOQL, but should not LIKE statement look this way: URL += "(complaint_type LIKE '%" + c_type + "%')"; ? — GSazheniuk, Nov 25 '15 at 17:57
To be honest I have no idea, @GSazheniuk, I'm familiar with CONTAINS. I'll try it. Thank you. — pceccon, Nov 25 '15 at 18:00
http://salesforce.stackexchange.com/questions/818/can-you-use-the-like-operator-in-soql-queries-via-the-rest-api have a look at this thread too. — GSazheniuk, Nov 25 '15 at 18:00
If you have a look at link in my second comment, they say that you should encode URL, so it should look this way in your case: URL += "(complaint_type LIKE '%25" + c_type + "%25')"; — GSazheniuk, Nov 25 '15 at 18:18
Does it work for you, @GSazheniuk? I still didn't figure out the problem. — pceccon, Nov 30 '15 at 20:41

score 1 · Accepted Answer · answered Dec 01 '15 at 03:31

1

I have just figured out how it works, it seems that it does not accept just percent symbols '%' in the condition, it should be preceded by backslash(or is it just slash, anyway).

So this is the valid URL for using 'like' statement:

http://data.cityofnewyork.us/resource/erm2-nwe9.json?$where=(latitude%20IS%20NOT%20NULL)%20AND%20(complaint_type%20like%20%27\%Noise\%%27)%20AND%20(created_date%3E=%272013-08-01%27)%20AND%20(created_date%3C=%272013-08-08%27)&$group=complaint_type,descriptor,latitude,longitude&$select=descriptor,latitude,longitude,complaint_type

Or appropriate row in your code:

URL += "(complaint_type LIKE '\\%" + c_type + "\\%')";

Let me know if it works, and sorry for not replying on time, I really have no experience with Salesforce and SOQL. But thanks to you there is another new space for me to explore :)

answered Dec 01 '15 at 03:31

GSazheniuk

1,340
10
16

Thank you, @GSazheniuk. I just copied your code and got a different encrypted URL: http://data.cityofnewyork.us/resource/erm2-nwe9.json?$where=(latitude%20IS%20NOT%20NULL)%20AND%20(complaint_type%20LIKE%20'%5C%25Noise%5C%25')%20AND%20(created_date%3E='2015-12-08')%20AND%20(created_date%3C='2015-12-01')&$group=complaint_type,descriptor,latitude,longitude&$select=descriptor,latitude,longitude,complaint_type That returns the same error (BAD REQUEST) :S – pceccon Dec 01 '15 at 13:31
That's strange, what browser are you using? I just copied my URL and pasted into Chrome and it worked. – GSazheniuk Dec 01 '15 at 14:26
I was trying using Safari, @GSazheniuk. I'll try in another one. I even post here, http://stackoverflow.com/questions/34022024/encode-url-returning-different-result-than-the-expected?noredirect=1#comment55800297_34022024, but I believe that this works for you. Thank you. (: – pceccon Dec 01 '15 at 14:29

LIKE or CONTAINS in SOQL Query

1 Answers1