"does not contain" in CAML?

Question

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?

Collected

Answer 1

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.

Lowerclassman

It does smell really bad, have you tried using a calculated column in addition to the rest of your query? – Hereditament

@AlexanderN A calculated column doesn't always work, for example if you are filtering on the Created field or other fields that are part of content types - those simply seem unavailable in the Column list. – Lowerclassman

Answer 2

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

Hereditament

There is an error in your formula. Correct formula is - =ISNUMBER(FIND("Critical",[Title]) – Dissected

Answer 3

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.

Thetic

Answer 4

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

Skive

That would be nice but there are many parts of the product built on it! – Kozlowski

CAML as a List Definition language (ONET.xml) is quite usable. CAML as an SQL Replacement is a joke, and not even a good one. – Lowerclassman

Answer 5

TRy Not Includes

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

Pignut