SOQL to Find Custom Labels Where the Value Contains a Specific String
Finding custom labels in Salesforce using SOQL based on their values requires a slightly indirect approach because SOQL doesn't directly query the Label field of the CustomLabel object. Instead, we can use the Name field which contains the API name of the label, and then filter based on the value using a wildcard. This method is efficient for searching when you know part of the API name or the desired value. Let's break down how to achieve this and explore alternative methods for different scenarios.
Understanding the Challenge
Directly querying a custom label's value using SOQL isn't possible. SOQL operates on the schema structure of the object, and the value of a custom label isn't stored as a field directly queryable in the CustomLabel object. The Label field displays the label's value in the UI, but it's not available for SOQL queries.
SOQL Querying for Partial Matches in Label Values (Indirect Method)
The most effective strategy utilizes the Name field (API Name) along with knowledge of your label's naming convention and value. Let’s assume you want to find labels where the value contains the string "My Value". If you know the API names typically begin with "MyLabel_", you might use the following:
SELECT Name, Label FROM CustomLabel WHERE Name LIKE 'MyLabel_%';
This query selects all custom labels whose names start with "MyLabel_" and then you'll have to iterate through the results in your Apex code or other applications to check if the Label (value) contains "My Value". This is not ideal, but it’s the closest you can get with SOQL alone. It requires you to have some knowledge about your custom label naming conventions.
Here's how you'd typically handle this in Apex:
List<CustomLabel> labels = [SELECT Name, Label FROM CustomLabel WHERE Name LIKE 'MyLabel_%'];
for (CustomLabel label : labels) {
if (label.Label.contains('My Value')) {
System.debug('Found Label: ' + label.Name + ', Value: ' + label.Label);
}
}
Important Consideration: This approach relies on a pattern in your label API names. If your naming is inconsistent, this method becomes less effective.
Alternative Approach: Using a Custom Metadata Type (Recommended)
For more robust and flexible searching of custom label values, consider storing your custom labels within a custom metadata type instead. This allows you to define fields that accurately reflect the information needed, and then allows you to use SOQL directly on the values.
Let’s assume you created a custom metadata type called MyCustomLabels__mdt with a field called LabelValue__c. Your query would look like this:
SELECT LabelValue__c FROM MyCustomLabels__mdt WHERE LabelValue__c LIKE '%My Value%';
This query directly finds all records in the custom metadata type whose LabelValue__c field contains "My Value." This is the most efficient and recommended way to search custom label values.
Frequently Asked Questions
Q: How do I find a custom label by its exact API name?
A: Use the following simple SOQL query replacing 'MyLabel__c' with the actual API name:
SELECT Label FROM CustomLabel WHERE Name = 'MyLabel__c'
Q: Can I use wildcards with the Label field in SOQL?
A: No, you cannot use wildcards directly with the Label field in SOQL queries. The Label field is only accessible through the Apex code's retrieved CustomLabel object.
Q: My custom labels have inconsistent naming. How can I search effectively?
A: Use a custom metadata type as this provides the most flexible and efficient way to search for values regardless of naming conventions. It eliminates the need to rely on the API name for filtering.
By understanding these approaches and choosing the method that best suits your naming conventions and needs, you can effectively find the custom labels you need based on their values. Using a custom metadata type is highly recommended for long-term maintainability and scalability.
