How to use SOQl contains?

1.8K Asked by ChristianParsons in Salesforce , Asked on Jun 5, 2023

My SOQL Query
string str = '%'+email.subject.trim()+'%'; GMC__c gmcList = [select id,name,Case_Number__c from GMC__c where name like :str limit 1];

GMC__c Name is an autonumber field which will be unique Now my actual requirement is when a user sends a mail Subject with only Name it works perfectly. In the other case if he says suppose This is regarding GMCName then we need to Query that contains GMCName. What would be the best approach for this

Answered by Darsh K

What I understood from your requirement to use soql contains is, Subjects sent by User can be dynamic. Sometimes it's only GMCName and sometimes it is included in some sentence, as you have mentioned This is regarding GMCName.

And your GMC__c Name is an autonumber field. So it must have some particular format like Q{00000} i.e A-{0000} etc. Having fixed length and fixed format. You must know the starting characters of your GMC Name.

You can do following :

Integer index = email.subject.indexOf(--Starting Characters of GMC Name--);

//As you know the length of your GMC name(as it is Autonumber), so that length to the Integer index obtained above.

String gmcName = email.subject.substring(index,index+lengthOfAutonumber)

In this way you can obtain GMCName

Your Answer

Answer (1)

Ranjana

In SOQL, the CONTAINS operator is used to perform full-text searches on a text field. This operator allows you to search for one or more words within a specified text field of an object.

Here’s the basic syntax of the CONTAINS operator in SOQL:

SELECT field1, field2 

FROM Object 

WHERE CONTAINS(fieldname, 'searchterm')

Parameters:

field1, field2: The fields you want to retrieve in the query results.
Object: The Salesforce object you are querying.
fieldname: The name of the text field you want to search within.
'searchterm': The term or terms you want to search for. Note that multiple search terms should be separated by spaces.

Example:

Let's say you have a custom object called Product__c, and it has a text field called Description__c. You want to search for products where the description contains the word "electronics".

SELECT Name, Description__c 

FROM Product__c 

WHERE CONTAINS(Description__c, 'electronics')

This query will return all products whose description contains the word "electronics".

Additional Considerations:

Case-Insensitive: By default, the CONTAINS operator is case-insensitive. This means it will match search terms regardless of case.
Word Boundary: The search term must match whole words in the text field. It won't match substrings within words.
Indexing Requirement: The field being searched must be indexed for full-text search. Text fields are indexed by default, but you may want to verify if custom fields are indexed if you encounter performance issues.
Search Term Limit: The search term can contain up to 4000 characters.

Example with Multiple Search Terms:

You can also search for records containing multiple terms. For example, to find products containing both "electronics" and "gadgets":

SELECT Name, Description__c 

FROM Product__c 

WHERE CONTAINS(Description__c, 'electronics AND gadgets')

This query will return products whose description contains both "electronics" and "gadgets".

Conclusion:

The CONTAINS operator in SOQL allows you to perform full-text searches on text fields within Salesforce objects. It's a powerful tool for finding records based on specific keywords or phrases contained within text fields.

6 Months