SharePoint : How to check for null with a CAML Query?

Question

I have this CAML:

query.Query = @"<Where><Eq><FieldRef Name='MessageID' /><Value Type='Text'></Value></Eq></Where>";

This checks if the value of MessageID = string.empty()

What I would like to check for is null.... not empty string...

Is this possible with CAML?

Wrapping an SQL-like querying language in XML: a stroke of genius, or pure malice? You decide. — Juliet, Aug 27 '09 at 14:08
It would work if it was SQL... but its another funky (broken) aspect of SharePoint. SharePoint never disappointments... — JL., Aug 27 '09 at 14:10
Also you have to love Microsofts documentation - http://msdn.microsoft.com/en-us/library/dd588322%28office.11%29.aspx - explains everything else except how to use isnull.... — JL., Aug 27 '09 at 14:11

score 57 · Accepted Answer · answered Aug 27 '09 at 13:58

57

CAML has the IsNull operator,so the query would be:

query.Query = @"<Where><IsNull><FieldRef Name='MessageID' /></IsNull></Where>"

answered Aug 27 '09 at 13:58

Colin

10,630
28
36

How does it not 'work' You could try using an Or statement, check for empty AND Null values? – Colin Aug 27 '09 at 14:09
P.S. what type is the field of anyway, it says MessageID. Is that an integer? – Colin Aug 27 '09 at 14:10
Its a string... but when I step through in code... and check the value... its null.... – JL. Aug 27 '09 at 14:11
Did you add this field to the list later on? Because SharePoint doesn't update Existing items when the field is added, I guess you could describe it as "the field exists but no value has been assigned, not even Null" – Colin Aug 27 '09 at 14:16
4

Cool! P.S. how I know these things? Mainly through many frustrated hours :-D – Colin Aug 27 '09 at 14:22

score 26 · Answer 2 · edited Nov 21 '12 at 09:01

26

Needed an equivalent to String.IsNullOrEmpty(Description). Ended up with this:

<And>
  <IsNotNull>  
    <FieldRef Name='Description' />   
  </IsNotNull>  
  <Neq>  
    <FieldRef Name='Description' />  
    <Value Type='Text'></Value>  
  </Neq>
</And>

edited Nov 21 '12 at 09:01

Echilon

10,064
33
131
217

answered Nov 21 '12 at 08:38

Stephane

261
3
2

6

note that it's negated IsNullOrEmpty before you copy and paste ;) – Paweł Bulwan Jul 16 '13 at 12:58

score 13 · Answer 3 · answered Jul 30 '13 at 10:21

Agree with Colin, and more often used condition are as following:

1. Null:
<Where><IsNull><FieldRef Name="CustomField" /></IsNull></Where>
2. Not Null:
<Where><IsNotNull><FieldRef Name="CustomField" /></IsNotNull></Where>
3. Equal:
<Where><Eq><FieldRef Name="CustomField" /><Value Type="Text">MatchValue</Value></Eq></Where>
4. Not Equal：
<Where><Neq><FieldRef Name="CustomField" /><Value Type="Text">MatchValue</Value></Neq></Where>
5. Greater Than：
<Where><Gt><FieldRef Name="CustomField" /><Value Type="Text">1</Value></Gt></Where>
6. Greater Than And Equal：
<Where><Geq><FieldRef Name="CustomField" /><Value Type="Text">1</Value></Geq></Where>
7. Lower Than:
<Where><Lt><FieldRef Name="CustomField" /><Value Type="Text">1</Value></Lt></Where>
8. Lower Than And Equal:
<Where><Leq><FieldRef Name="CustomField" /><Value Type="Text">1</Value></Leq></Where>
9 Begin With:
<Where><BeginsWith><FieldRef Name="CustomField" /><Value Type="Text">StartString</Value></BeginsWith></Where>
10: Contains:
<Where><Contains><FieldRef Name="CustomField" /><Value Type="Text">ContainString</Value></Contains></Where>

Note: More information please visit: http://msdn.microsoft.com/en-us/library/ms467521.aspx There is the fully Caml Query Schema.

Hope this can help you~

score 0 · Answer 4 · answered May 31 '19 at 11:22

You can Check for Nulls using: IsNotNull element (Query).

Used within a query to return items that are not empty (Null).

Example:

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

You can Check for Nulls using: IsNull element (Query).

Used within a query to return items that are empty (Null).

Example:

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

SharePoint : How to check for null with a CAML Query?

4 Answers4