In this blog we will learn how to use like operator in FetchXML?

Use like operator in FetchXML

The LIKE operator is used to search for a specified pattern in a column.

Generally There are two wildcards often used in conjunction with the LIKE operator:

1. The percent sign (%) represents zero, one, or multiple characters
2. The underscore sign (_) represents one, single character

FetchXML LIKE Examples
The following FetchXML selects all customers with a CustomerName starting with “a”:

<fetch mapping="logical" version="1.0">
  <entity name="customers">
    <all-attributes />
    <filter>
      <condition attribute="customername" operator="like" value="a%" />
    </filter>
  </entity>
</fetch>

The following fetchxml selects all customers with a CustomerName ending with “a”:

<fetch mapping="logical" version="1.0">
  <entity name="customers">
    <all-attributes />
    <filter>
      <condition attribute="customername" operator="like" value="%a" />
    </filter>
  </entity>
</fetch>

The following FetchXML selects all customers with a CustomerName that have “xm” in any position:

<fetch mapping="logical" version="1.0">
  <entity name="customers">
    <all-attributes />
    <filter>
      <condition attribute="customername" operator="like" value="%xm%" />
    </filter>
  </entity>
</fetch>

The following fetchxml selects all customers with a CustomerName that have “a” in the second position:

<fetch mapping="logical" version="1.0">
  <entity name="customers">
    <all-attributes />
    <filter>
      <condition attribute="customername" operator="like" value="_a%" />
    </filter>
  </entity>
</fetch>

The following fetchxml selects all customers with a CustomerName that starts with “c” and are at least 3 characters in length:

<fetch mapping="logical" version="1.0">
  <entity name="customers">
    <all-attributes />
    <filter>
      <condition attribute="customername" operator="like" value="c__%" />
    </filter>
  </entity>
</fetch>

The following fetchxml selects all customers with a ContactName that starts with “a” and ends with “n”:

<fetch mapping="logical" version="1.0">
  <entity name="customers">
    <all-attributes />
    <filter>
      <condition attribute="contactname" operator="like" value="a%n" />
    </filter>
  </entity>
</fetch>

The following fetchxml selects all customers with a CustomerName that does NOT start with “a”:

<fetch mapping="logical" version="1.0">
  <entity name="customers">
    <all-attributes />
    <filter>
      <condition attribute="customername" operator="not-like" value="a%" />
    </filter>
  </entity>
</fetch>