CAML query using 'contains'

Question

I have a CAML query

<Query><Where><Or>"+
             "<Eq><FieldRef Name='UserName' /><Value Type='User'>" + User.Name + "</Value></Eq>"+
             "<Contains><FieldRef Name ='OtherUsers'/><Value Type = 'Note'>" + site.CurrentUser.Name + "</Value></Contains>"+
          "</Or></Where></Query>

This works and brings desired results. Now coming to the issue

The OtherUsers column holds the list of selected users in .csv format. Say if User1&User11 are selected the column will contain User1,User11

When User11 is selected the column will just have User11

Now according to the query, when User1 logs in, it will bring the record containing User11 in OtherUsers column as User11 contains User1. How can I overcome this issue?

Answer 1

"<Query><Where>"+
                "<Or>"+
                    "<Or>"+
                        "<Or>"+
                             "<Eq><FieldRef Name='UserName' /><Value Type='User'>" + User.Name + "</Value></Eq>"+
                             "<Contains><FieldRef Name ='OtherUsers'/><Value Type = 'Note'>" + site.CurrentUser.Name + ",</Value></Contains>"+
                        "</Or>"+
                        "<Contains><FieldRef Name='OtherUsers' /><Value Type='Note'>," + site.CurrentUser.Name + "</Contains>"+
                    "</Or>"+
                "<Eq><FieldRef Name='OtherUsers' /><Value Type='Note'>" + site.CurrentUser.Name + "</Value></Eq>"+
                "</Or>"+
            "</Where></Query>"

Use this query. It should do the trick

If user1 logs in below scenarios It will work on:

• OtherUsers = "user1,user11,user12";
• OtherUsers = "user2,user1,user12";
• OtherUsers = "user2,user1";
• OtherUsers = "user1";

And for User11 it will work on below scenarios only:

• OtherUsers = "user1,user11,user12";
• OtherUsers = "user2,user11";
• OtherUsers = "user11";

Answer 2

I had also same problem, But unfortunately you cant check exact match of string in comma separated value of multilne text.

But alternatively you can achieve this,

1. Use contains operator that gives you a set of result items,
2. Then split items separated by comma and check exact match using script/c#/regular expression.

Answer 3

I think your value type is wrong. I have developed such kind of CAML query like below:

<Contains><FieldRef Name='otherUser' /><Value Type='User'>" + site.CurrentUser.Name + "</Value></Contains>

Thanks

Answer 4

You could also use "In" element

<In>
  <FieldRef Name = "Field_Name"/>
  <Values>    <Value Type = "Field_Type"/>
  </Values>  <XML />
</In>

Ref: https://learn.microsoft.com/en-us/sharepoint/dev/schema/in-element-query