Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Ross Hopkins 8Ross Hopkins 8 

Using LIKE in a SOQL Query - no viable alternative at character '%'

Hi,
I'm trying to lookup an Account ID based on a text value.

Visitors who submit a form on our website can provide a "Dealer Name" value in a free text field. I want to use that value to search for an AccountID based on if the web visitor provided text is LIKE an Account.Trading_Name__c.

The code is as follows: 
trigger Shopify_Set_Dealer_Account on Opportunity (before insert) {
    for(Opportunity o: trigger.new){
    
        /* 
            Only look up and set a Dealer_Account__c Id value if a Dealer Name text value is present, 
            this is a CUE Rental Opp and we haven't somehow set an Id before.
            Oppty_Dealer_Name__c is a Dealer Name text value provided in forms by visitors to SC websites
        */
        
        if((o.Oppty_Dealer_Name__c != NULL) && (o.Name.contains('CUE Rental') && (o.Dealer_Account__c == NULL))){
        
            // Construct SOQL query as a string variable first, as Apex doesn't make it easy to use LIKE %<variable>% syntax
            string Query = 'SELECT Id from Account WHERE Trading_Name__c LIKE' + '/%' + o.Oppty_Dealer_Name__c+ '/%' + 'LIMIT 1';
            
            // Execute the query
            Account[] DealerAccount = Database.query(Query); 
            
            /* 
                Only assign the found Dealer Account Id if we were able to match the website-provided Dealer Name value to a Dealer Account 
                Trading Name value, otherwise we'll get an error when we try to access the array element
            */
            if (DealerAccount.size() == 1) {
                o.Dealer_Account__c = DealerAccount[0].Id;
            }
            
        }
    }
}
However, I get the following error when this trigger is executed: 
Shopify_Set_Dealer_Account: execution of BeforeInsert caused by: System.QueryException: line 1:49 no viable alternative at character '%' ().
I'm assuming this relates to lines 13 or 16.
I've searched online a lot for this problem and followed advice I'd seen about constructing the query first, but I'm having no joy.
Any advice?
Thanks,
Ross
 
Best Answer chosen by Ross Hopkins 8
Ross Hopkins 8Ross Hopkins 8
Hi Lokesh, Ramesh,

I very much appreciate your attempts to help me.
I found another syntax suggestion on StackOverflow and this is working for me. Lines 13-16 have been replaced with:
  
List<Account> DealerAccount = [SELECT Id FROM Account WHERE Trading_Name__c LIKE :('%' + o.Oppty_Dealer_Name__c + '%')];

I don't think thats the kind of syntax you'd guess right!

And Lokesh, yes, the Oppty_Dealer_Name__c value does contain multiple words.

Thanks,
Ross

All Answers

Ramesh DRamesh D
I don't see what else could be wrong here. I think you copied it from another program that uses a different quote character? Try deleting your quote character and replacing it with a new one. Sometimes that helps, odd as it sounds.

Thanks
Ramesh
Lokeswara ReddyLokeswara Reddy
Hi Ross,
It could Salesforce SOQL issue - especially when you use LIKE key word and pass mulitple words in it. I'm having issues in searching DataCloudcompany object.

Does your field Oppty_Dealer_Name__c contains multiple words in it!
Try running the same query in workbench with same value and check for the results.

Lokesh...
 
Ross Hopkins 8Ross Hopkins 8
Hi Lokesh, Ramesh,

I very much appreciate your attempts to help me.
I found another syntax suggestion on StackOverflow and this is working for me. Lines 13-16 have been replaced with:
  
List<Account> DealerAccount = [SELECT Id FROM Account WHERE Trading_Name__c LIKE :('%' + o.Oppty_Dealer_Name__c + '%')];

I don't think thats the kind of syntax you'd guess right!

And Lokesh, yes, the Oppty_Dealer_Name__c value does contain multiple words.

Thanks,
Ross
This was selected as the best answer