SharePoint CAML query - to retrieve column value with certain pattern from Library

Question

I need to query from pages library to get a HTML column. This column contains rich text and references to various URLs(anchor tag). The query should return items which contain a column value with hyperlink to certain URL Pattern.

For ex: Column contains "http://example.com/my-sites/......[UniqueIDWhichVaries].aspx"

Below is my caml query:

   <ViewFields>
      <FieldRef Name='MyColumn' />
   </ViewFields>
   <Where>
      <Contains>
         <FieldRef Name='MyColumn' />
         <Value Type='HTML'>a href=http://example.com/my-sites/</Value>
      </Contains>
   </Where>
   <QueryOptions>
      <ViewAttributes Scope='RecursiveAll' />
   </QueryOptions>

This query does not return expected results.

I would like to know what is the best way to retrieve such items. I need to use this query in a power shell script.

Can anyone help me here, please?

Thanks in advance!!!

Answer 1

To put this in powershell you can use the following reference: https://gallery.technet.microsoft.com/office/How-to-do-a-CAML-Query-6f5260cf

Answer 2

Please try this below query:

<ViewFields>
      <FieldRef Name='MyColumn' />
   </ViewFields>
   <Where>
      <Contains>
         <FieldRef Name='MyColumn' />
         <Value Type='Note'>http://example.com/my-sites/......[UniqueIDWhichVaries].aspx</Value>
      </Contains>
   </Where>
   <QueryOptions>
      <ViewAttributes Scope='RecursiveAll' />
   </QueryOptions>

You can encode the square brackets using their unicode value in query:

http://example.com/my-sites/......[UniqueIDWhichVaries].aspx

Hope this will help you!