When we use the wild card with CONTAINS predicate on the managed property “PreferredName” (OOB); it does not returns any result
Issue Description
- When we use the wild cards with contains predicate on the managed property “PreferredName” (OOB); it DOES NOT RETURNS any RESULT
- EXAMPLE: SELECT Title, FirstName, LastName, PreferredName, JobTitle FROM portal..scope() WHERE ( ("SCOPE" = 'People') ) AND CONTAINS(PreferredName,'anjali*')
Analysis
I checked on the queries & found that the PreferredName property does not works with the CONTAINS (with or without WILD card characters) & FREETEXT predicate
- EXAMPLE: SELECT Title, FirstName, LastName, PreferredName, JobTitle FROM portal..scope() WHERE ( ("SCOPE" = 'People') ) AND CONTAINS(PreferredName,'anjali*')
- EXAMPLE: SELECT Title, FirstName, LastName, PreferredName, JobTitle FROM portal..scope() WHERE ( ("SCOPE" = 'People') ) AND FREETEXT(PreferredName,'anjali')
This seems to be an issue with SharePoint 2007
Resolution/ WorkAround
- Use LIKE Predicate with Managed Property: PreferredName (Note: LIKE Predicate support WILD card characters)
EXAMPLE: SELECT Title, FirstName, LastName, PreferredName, JobTitle FROM portal..scope() WHERE ( ("SCOPE" = 'People') ) AND PreferredName LIKE 'anjali%') - Use CONTAINS Predicate (with or without WILD card characters) with Managed Property: Title (Note: Title (in SharedServices) is mapped to People:PreferredName)
EXAMPLE: SELECT Title, FirstName, LastName, PreferredName, JobTitle FROM portal..scope() WHERE ( ("SCOPE" = 'People') ) AND CONTAINS(TITLE,'anjali*')
Important Points To Consider
- Basic design/working for LIKE and CONTAIN Predicate
- Defining:
- LIKE Predicate- In simple words, if we define: It matches a pattern of string. It’s more like regular expression
- CONTAINS Predicate- In simple words, if we define: It searches for specific string in each n every word
- Example:
Property: test1=”abc dabch abcp” (In other words, Managed Property: test1 has value =”abc dabch abcp”)
Now,- CONTAINS(test1, ‘abc*’) : This will search each n every word in the string and will try to match with the given pattern that is ‘abc*’ (abc followed by zero or n number of characters). It finds 3 matches. It checks 3 words individually
- LIKE test1 ‘abc%’ : This will check for a pattern in test1 managed property. It checks the strings beginning with ‘abc’ followed by zero or n number of characters. It checks as regular expression. It doesn’t here check each word
- Analysis from above example
- In current example, LIKE predicate is more faster as compared to CONTAINS predicate. Better from performance point of view. We may end up concluding this most of the time.
- BUT, there may be scenarios where both may perform similarly. It greatly depends on the pattern of data to be searched.
- Important point is; both have been designed for different purposes; so which to be used depends on the requirement.
- Defining:
- Analyze the difference in 2 resolution
From above discussion: We can conclude- Both resolutions can be used ; depend upon the pattern of search.
- If we are sure of a particular pattern then go for LIKE predicate. Faster
- If no particular pattern and wants to check for specific words example in fields such as Comments ; Description then choose CONTAINS Predicate.