Apex Code Development You need to sign in to do that
Don't have an account?
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:
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
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
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
Thanks
Ramesh
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...
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