function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
shrutiiiiishrutiiiii 

SOQL Like query to search whole word

Hi,

While capturing a lead by Lead Capture Form we are checking whether Lead exists in existing accounts data. For this we are reducing the Lead compnay name by exluding few keywords (Inc, Technology, Llc etc) and then searching it in Accounts using like query.

e.g. Compnay name of inserted Lead is "Macro Tech Inc" then reduced compnay name is "macro" and SOQL query is "Select Id from Account where Name Like '%macro%'". Query returns these 2 companies - Macronetics and Macro Technology. Our code then picks first compnay i.e. Macronatics. Which is wrong, 'Macro Tech Inc' and 'Macronetics' are different companies. So there is requirement to search by whole work. %macro% returns all Acounts where the string is a substring of any account name.

Please help in writing the query to get accounts where I can search by whole word to find near match. Or do you have any better idea to search for near match of account?

Thanks,
Shruti
Bhanu MaheshBhanu Mahesh
Hi Shruti,

If Lead is the incoming lead, use the below query to get the accounts with same name as lead company for exact company
List<Account> accs = [SELECT Id, Name FROM Account WHERE Name = :Lead.Company];

Regards,
Bhanu Mahesh
shrutiiiiishrutiiiii
Bhanu,

I don't want to do exact match. I need to search for near match. Please read my post again. I want to search for whole word that does not mean whole Lead Company Name. That means reduced lead company name into account name.

For string 'macron' the query should return accounts - 'Macron Inc', 'Macron Technology', 'The Macron Tech' and 'Macron'. But it should NOT return 'Macrometics'. Forget about lead and account. if you know only help me how to make a query call to search for whole word.

Thanks
Ramesh KosalairamanRamesh Kosalairaman

Hi Shruti,

this link will give you some idea 

https://developer.salesforce.com/forums/ForumsMain?id=906F000000094P9IAI 

 
ManojjenaManojjena
Hi Shruti,

Just try this I think it will work for you
.
SELECT Name FROM Account WHERE Name LIKE ' Macro %'
add on espace after Macro for which it will only return the records which will start with Macro not macroo  or Macrotech.

I think it will solve your problem .

 
Karthik Reddy 6Karthik Reddy 6
Why don't you just create a formula field to capture the company name till the 1st occurence of space. For eg: If Company is Macron Inc. your formula field should be able to capture "Macron". Now run a query based on it Like

SELECT Name FROM Account WHERE Name =: Lead.Formula_Field.

Let me know if it works.
 
Karthik Reddy 6Karthik Reddy 6
Sorry for the incorrect query. Here is the right one SELECT Name FROM Account WHERE Name LIKE Lead.Formula_Field+'%'
shrutiiiiishrutiiiii
Hi,

Thanks everyone for the reply. The "Macron" example I gave, is just an example. Sometimes there are 2 or more words by which I need to search for account e.g. Hitachi Kokusai America. I need to search for account which contains all 3 words - Hitachi, Kokusai and America. Query here then should not return account name with word American. There should be word match. Plus the query should also work if there is a single word.

I tried a lot with different combination of %. But no use.

Thanks