1

I've a query executed using database.query() method. The query looks like this:

Select Id, Name from CustomObj__c WHERE Name like '%admin, ray%' ORDER BY Name

I can execute this query directly in 'Developer Console' and I get 0 results as well.

But there is definitely a record named Admin, Raymond in CustomObj__c. There is No Private Sharing in the org. CustomObj__c is set to Public Read/Write setting.

The number of records is not too much in the object as well, approximately 14000 record exist in the custom object.

The record was created approximately 3 weeks back, so it should comes down to data not indexed. And plus I can search for this record in Salesforce Search as well as I can open up and access this record as well.

if I can query it like this:

1 record returned:

Select Id, Name from CustomObj__c WHERE Name like '%admin, %' ORDER BY Name

Select Id, Name from CustomObj__c WHERE Name like '%admin%' ORDER BY Name

Select Id, Name from CustomObj__c WHERE Name like '%ray%' ORDER BY Name

0 record returned:

Select Id, Name from CustomObj__c WHERE Name like '%admin, ray%' ORDER BY Name

Can someone point me if there is something wrong in query at all or could it be platform issue of sort?

EDIT: Added a bit of code context: My query generation is like this:

list<sObject> lst = (list<sObject>)database.query( 'Select Id,'+string.escapeSingleQuotes(nameField)+' from '+string.escapeSingleQuotes(obj)+' where ' + string.escapeSingleQuotes(nameField)+' like \'%'+string.escapeSingleQuotes(q)+'%\'' );

And it results into following actual SOQL query:

Select Id, Name from CustomObj__c WHERE Name like '%admin, ray%' ORDER BY Name

6
  • 1
    I would suggest that it is something to do with having whitespace in the name. Are you sure you are using the same whitespace? Have you tried matching with '%admin%ray%;?
    – Phil W
    Oct 3 '19 at 14:13
  • 1
    Are you sure the record returned when using '%ray%' is the same as the one returned by '%admin%'?
    – rael_kid
    Oct 3 '19 at 14:16
  • Yes. I'm sure, because my tool is using auto complete, and rest of the name shows up there as well in result in my query in many cases except the one highlighted above.
    – VarunC
    Oct 3 '19 at 14:22
  • Are you using a binding variable when passing the match string to your query? Could you please update your question and show the exact code you are using since it could be something more to do with how you construct the query rather than the specific query text that is at fault.
    – Phil W
    Oct 3 '19 at 14:27
  • added my query generation code in query. But I'm still puzzled as I'm getting same result of no records from My Code and as well as from Developer Console SOQl execute.
    – VarunC
    Oct 3 '19 at 14:50
2

This happens when you have additional spaces in between the words i.e. between Admin and Raymond. This space doesn't show up in the page layout nor in the developer console but when you edit that record it will display the additional spaces which prevents this records being caught in the LIKE query of the above mentioned SOQL.

So taking your same example, when I queried using below SOQL

Select Id, Name from Account WHERE Name like '%Admin, ray%' ORDER BY Name

It returned zero results

enter image description here

Now, when I changed the query to

Select Id, Name from Account WHERE Name like '%Admin%ray%' ORDER BY Name

This yielded a result, which showed name as Admin, Raymond. Note that there is no additional space displayed in developer console result.

enter image description here

Then when I when into the detail layout of Account, name is displayed as Admin, Raymond enter image description here

But, when I edited Account Name, it displayed as below i.e. with additional spaces.

enter image description here

Now, I changed my SOQL to below i.e with additional spaces i.e copied value from edit mode.

Select Id, Name from Account WHERE Name like '%Admin,    ray%' ORDER BY Name

It yielded the results properly

enter image description here

**

Conclusion

So to get the proper results, it would be better to replace the spaces in the search string with % i.e. you can replace your dynamic SOQL from below

list<sObject> lst = (list<sObject>)database.query( 'Select Id,'+string.escapeSingleQuotes(nameField)+' from '+string.escapeSingleQuotes(obj)+' where ' + string.escapeSingleQuotes(nameField)+' like \'%'+string.escapeSingleQuotes(q)+'%\'' );

with

list<sObject> lst = (list<sObject>)database.query( 'Select Id,'+string.escapeSingleQuotes(nameField)+' from '+string.escapeSingleQuotes(obj)+' where ' + string.escapeSingleQuotes(nameField)+' like \'%'+string.escapeSingleQuotes(q.replaceAll(' ', '%'))+'%\'' );

Note that I have added replaceAll in this code i.e. string.escapeSingleQuotes(q.replaceAll(' ', '%')). This above dynamic query now will return the results with additional spaces as well.

0

I think you have an error in your SOQL. It should looks like the following in Apex:

 String str = '%admin, ray%'
 List<Cust_obj> accts = [ SELECT Id, Name FROM Cust_obj WHERE Name LIKE :str];
3
  • Better to have "String match = '%admin, ray%'; List<Cust__c> custs = [SELECT Id, Name FROM Cust__c WHERE Name LIKE :match];" What's more this approach will work directly if having to move to dynamic (rather than static) SOQL.
    – Phil W
    Oct 3 '19 at 14:12
  • @PhilW fair enough. updated. Oct 3 '19 at 14:18
  • I think I'm past that. Coding wise, I had to use Dynamic APEX for dynamic nature of application. Plus, I'm not able to execute the query in Developer Console and return record as well, which kind of over shadow coding issues :(
    – VarunC
    Oct 3 '19 at 14:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.