FetchXML Tutotorial

How to use like operator in FetchXML

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>

Leave a Reply

Your email address will not be published. Required fields are marked *