When constructing queries in FetchXML that involve string conditions, utilizing wildcard characters can greatly expand your search capabilities and flexibility. This blog post will guide you through the different operators you can use with wildcard characters and explain each character’s functionality, complete with relevant examples.
Searching for Strings with Literal Wildcard Characters
Sometimes, you might need to search for strings that contain characters typically recognized as wildcards. To treat wildcard characters as literals (meaning the character itself rather than its wildcard functionality), enclose them in square brackets. For example, to search for a percent sign (%) in a name, your query would look like Name like [%]%.
Operators for Wildcard Usage
FetchXML supports several operators that work effectively with wildcard characters for string value conditions:
- like: Searches for a pattern anywhere within a string. Example: To find all contacts whose names contain “sam”, regardless of position
- not-like: Finds records where the pattern does not match anywhere within a string.
- begins-with: Filters records starting with the specified pattern.
- not-begin-with: Selects records that do not start with the specified pattern.
- ends-with: Identifies records ending with the specified pattern.
- not-end-with: Locates records that do not end with the specified pattern.
Wildcard Characters and Their Uses
Here are the wildcard characters you can use in FetchXML to enhance your query’s condition checking:
% (Percent): Matches any string of zero or more characters. It can be used as a prefix or suffix. Example: %North% matches any string containing “North”.
<fetch>
<entity name="contact">
<attribute name="fullname" />
<filter>
<condition attribute="fullname" operator="like" value="%sam%" />
</filter>
</entity> </fetch>
_ (Underscore): Matches any single character. Useful for replacing just one unknown character. Example: N_rth could match “North” or “Narth”.<fetch> <entity name="contact"> <attribute name="fullname" /> <filter> <condition attribute="fullname" operator="like" value="s_m%" /> </filter> </entity> </fetch>
[] (Square Brackets): Matches any single character listed within the brackets. This range can specify a sequence of characters. Example: N[oa]rth matches “North” or “Narth”.<fetch> <entity name="contact"> <attribute name="fullname" /> <filter> <condition attribute="fullname" operator="like" value="s[n-z]m%" /> </filter> </entity> </fetch>
[^] (Caret within Square Brackets): Excludes any single character specified within the brackets, matching characters not listed. Example: N[^oa]rth would not match “North” or “Narth” but could match “Nirth”.<fetch> <entity name="contact"> <attribute name="fullname" /> <filter> <condition attribute="fullname" operator="like" value="s[^b-g]%" /> </filter> </entity> </fetch>
Understanding and leveraging these wildcard characters in your FetchXML queries can significantly enhance your data retrieval processes by providing a flexible, powerful approach to search and filter operations in Dynamics 365. Whether you’re aiming to pinpoint specific data points or cast a wider net, these tools are indispensable for sophisticated querying.
My Trial 