Hallo,
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?
Thanks in advance!
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.
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.
@drax: let's hope CAML goes away - period. It is definitely one of the worst aspects of current SP programming.
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