Searching for multiple values in a field with CAML, Sharepoint 2013

Question

We have a SharePoint 2013 list that stores a list of work hazards. One of the columns is called "Hazard" and contains words which relate to the type of hazard it is.

Two Examples of data in the Hazard column are:

"Cross country line patrol / physical injury"

or

"Stringing conductor with stringing equipment / approach road / road /driveway / crossing / physical injury"

In SharePoint, this column is a single line of text. Notice the above two examples have the words "cross" in them.

My question is, how can I create a CAML query to return both results if a user was to search based on the Hazard column for "Cross Country". If I use "contains", it only returns the first example, not the second.

Thanks in advance.

Answer 1

There could be two ways to do it:

1. Use Search to find records. First instinct is to use "Search" instead of CAML query as search normalizes words for variations. However, not in this case. "Cross" and "Crossing" are different words in English language. and therefore Search takes them as different words and only gives result where they occur in as whole.

2. Use CAML query as you originally planned but after separating words like:

   <Where> <Or> <Contains> <FieldRef Name="Hazard"/> <Value Type="Text">Cross</Value> </Contains> <Contains> <FieldRef Name="Hazard"/> <Value Type="Text">Country</Value> </Contains> </Or> </Where>

   Major issue with this approach would be that rows with exactly "Cross Country" will not come on top. Further, you will need to nest "OR" clauses for each additional word searched.

Answer 2

Use Contains element.

SPQuery query = new SPQuery();
query.Query = "<Where><Contains><FieldRef Name="YOURFiledInternalName" />
   <Value Type="Text">cross</Value>" + 
      "</Contains></Where>";

Answer 3

We are using a product called Skybow and after contacting the company, they told me that my query was not possible but will be releasing a minor update which includes a fix to allow the use of "contains all".