3

Suppose i want to replicate following Sql condition in CamlQuery in JSOM

The Condition is:

WHERE
    @StudName IS NULL  OR [StudentName] LIKE '%'+@StudName+'%'

In above condition the @StudName contains the any character/character set. [StudentName] will be the Column Name in table.

I want to convert it in JSOM CamlQuery!

5

In CAML Query, there is no possible criteria as LIKE, so you can use Contains or BeginsWith

Look at this threads/article:

SharePoint list CAML query using CONTAINS

Using CAML "BeginsWith" in SharePoint

Also you need to check your StudName field as a null or not before going for CAML query. So consider below conversion for your CAML query:

function retrieveListItems() {
  if(StudName != null){ // StudName is the variable of your column StudName
    var clientContext = new SP.ClientContext(siteUrl);
    var oList = clientContext.get_web().get_lists().getByTitle('Your List Name here');

    var camlQuery = new SP.CamlQuery();
    camlQuery.set_viewXml("<View>" +
                              "<Query>" + 
                                  "<Where>"+
                                      "<Contains>"+
                                          "<FieldRef Name='Title' />"+
                                          "<Value Type='Text'>" + StudName + "</Value>" +
                                      "</Contains>" +
                                   "</Where>" + 
                              "</Query>" +
                              "<RowLimit>10</RowLimit>" + 
                          "</View>");
    this.collListItem = oList.getItems(camlQuery);

    clientContext.load(collListItem);

    clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));
  }
}
function onQuerySucceeded(sender, args) { 
    // Your Code Here 
}
function onQueryFailed(sender, args) { 
    // Your Code Here 
}

Update: Check for multiple fields with Contains CAML query is as below:

<Or>
    <Or>
        <Contains>
           <FieldRef Name='Field1' />
           <Value Type='Text'>2</Value>
        </Contains>
        <Contains>
           <FieldRef Name='Field2' />
           <Value Type='Text'>4</Value>
        </Contains>
    </Or>
    <Or>
        <Contains>
           <FieldRef Name='Field3' />
           <Value Type='Text'>2</Value>
        </Contains>
        <Contains>
           <FieldRef Name='Field4' />
           <Value Type='Text'>4</Value>
        </Contains>
    </Or>
</Or>

Hope this helps!

  • Can we pass more than <FieldRef> in between <Contains>? I just try it out for more than one <FieldRef> in between <Contains> but getting error! If there is a solution for multiple Fields, please let me know! – Y. Jagtap May 31 '17 at 11:06
  • I have 4 Fields to add in Contains! How can I do this? – Y. Jagtap May 31 '17 at 11:32
  • Check my updated answer! – Pradip R. May 31 '17 at 12:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.