CODEWITHSUNDEEP
sharepointcamllistitem
user4531
user4531

Reputation: 2565

"does not contain" in CAML?

In CAML I can query SharePoint Listitems using the "Contains"-element, but there is no "does not contain"-element I could use.

So what is the best way to get the items that do not contain a string? Is there a better way than to loop through each and every item?

Upvotes: 8

Views: 17104

Answers (5)

Alex Nolasco
Alex Nolasco

Reputation: 19466

So what is the best way to get the items that do not contain a string?

Try using a calculated column to reflect the value you are looking by creating the opposite value.

For example, say the column is called IsCritical. Then, add the column as a "YES/NO" and the formula as

=ISNUMBER(FIND("Critical"), [Title])

Then in your CAML query

<Query>
<Where>
   <Eq>
     <FieldRef Name='IsCritical'/>
     <Value Type='Boolean'>0</Value>
   </Eq>
</Where>
</Query>

A 0 in this query kinda reflects "Is Not Critical". However I am not sure what the performance may be as opposed to having a native CAML "Not Containts" which unfortunately does not exist.

See Also CAML Query Schema at MSDN

Upvotes: 3

Daniel
Daniel

Reputation: 635

TRy Not Includes

<NotIncludes>
<FieldRef Name='FileLeafRef' />
                                            <Value Type='Text'>stringvalue</Value>
                                        </NotIncludes>

Upvotes: 0

Oskar Austegard
Oskar Austegard

Reputation:

@drax: let's hope CAML goes away - period. It is definitely one of the worst aspects of current SP programming.

Upvotes: 0

Marian Polacek
Marian Polacek

Reputation: 2925

This problem with 'Contains' and 'BeginsWith' bothers me also. I hope that in next version of Sharepoint caml will be extended to be real tool, not just a rock on our leg.

The way I do it is to specify query as much as possible and then filter rows which don't match conditions in C# code. It is quite ugly sometimes as you sometimes have to process 100 rows to end with 1 result that match conditions.

Upvotes: 0

Michael Stum
Michael Stum

Reputation: 180994

The same restriction applies to BeginWith. I do not know any good solution sadly. What you could do: Do a Contains-Query, loop through each item and get the IDs, then do another big query for "ID NotEqual 1 or ID NotEqual 2 or ID NotEqual 3......" Since ID is indexed as far as I know, that should have a smaller impact on the database, but it still smells really bad.

For small list it does not matter, for larger lists i'd use the SQL Server Profiler to see what the impact is.

Upvotes: 4

Related Questions