CAML Queries For SharePoint Fields

Collaborative Application Markup Language (CAML) is an XML based markup language to define queries against list data. You can use CAML query to get the filtered, grouped or sorted data from SharePoint list.  CAML query can be used to retrieve SharePoint data in SharePoint object model, Web Service as well as PowerShell.
Following is the basic structure of CAML query:


Followings are the operators to use under Where element:

Logical Operators

And Or are the logical operators you can use to filter the data on multiple fields.

Comparison Operators

Operator	Meaning
Eq	= (Equal To)
Neq	<> (Not Equal To)
Lt	< (Less Than)
Gt	> (Greater Than)
Geq	>= (Greater Than Or Equal To)
Leq	<= (Less Than Or Equal To)
Contains	Like
IsNull	Null
IsNotNull	Not Null
BeginsWith	Beginning with the word
DateRangesOverlap	Compare the dates in a recurring event with a specified DateTime value, to determine whether they overlap

Order/Group Operators

Operator	Meaning
OrderBy	Specify the sort order. Query can be sort by multiple fields.
GroupBy	Specify the grouping for data. Data can be group by multiple fields

Followings are few snippets of CAML query to filter on the different types of fields in SharePoint. 

Text, Choice, Number, Currency, Boolean

Replace the DataType in the query. Followings are the datatypes:

DataType	Field
Text	Single line of text
Choice	Choice field (menu to choose from)
Number	Number field (1, 1.0, 100)
Currency	Currency field ($, ¥, €)
Boolean	Yes/No field (check box) 1 = Yes 0 = No

Date and Time

DateTime datatype can be used with multiple options. It can work with provided Date/DateTime or time part Today

Using Today

Lookup

Person

By ID

By Name

Taxonomy

Creating SharePoint Columns Programatically Using C#

As a SharePoint developer you can use SharePoint API to create columns programmatically in Lists or Document Libraries. You can set the different properties for the column. Followings are few snippets to create SharePoint column programmatically using C#.

Single line of text

Represents a column that contains a single line of text.

Multiple lines of text

Represents a column that contains a multiple line of text.

Choice (menu to choose from)

Represents a column that contains choices. It can be single select or multiple select. For single select Choice field renders Dropdown list or Radio button list and for multiple select it renders Checkbox list.

Number (1, 1.0, 100)

Represents a column that contains numbers. You can use DisplayFormat property to set the allowed number of decimal.

Currency ($, ¥, €)

Represents a column that contains currency. You can use DisplayFormat property to set the allowed number of decimal. You can use CurrencyLocaleId property to set the currency symbol. You can find full list of Locale Ids here

Date and Time

Represents a column that contains a DateTime value. You can use DisplayFormat property to set the date format.

Lookup (information already on this site)

Represents a column that contains a information from other List/Document Library from current site.

Yes/No (check box)

Represents a column that contains Boolean value.

Person or Group

Represents a column that contains a user or group information.

Hyperlink or Picture

Represents a column that contains a Url information. It can be Hyperlink or Picture Url.

Calculated (calculation based on other columns)

Represents a column that contains a calculated value based on other columns.

Managed Metadata

Represents a column that contains information from Term Store.

SPListItem Extension Methods To Get And Set Field Values

Followings are the extension methods for SPListItem class.

Single line of text, Multiline lines of text and Choice(Single)

Choice (Multiple)

Number, Currency

DateTime

Lookup (Single)

Lookup (Multiple)

Yes/No

User

User Collection

Hyperlink or Picture

Managed Metadata (Single)

Managed Metadata (Multiple)

You can download SPListItemExtensions.cs file containing all above extension methods.