When we use the wild card with CONTAINS predicate on the managed property “PreferredName” (OOB); it does not returns any result

  • Article

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

  1. 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')

  2. This seems to be an issue with SharePoint 2007

     

Resolution/ WorkAround

  1. 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%')
  2. 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
    1. 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
    2. 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
    3. 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.
  • Analyze the difference in 2 resolution 
         From above discussion: We can conclude
    1. Both resolutions can be used ; depend upon the pattern of search.
    2. If we are sure of a particular pattern then go for LIKE predicate. Faster
    3. If no particular pattern and wants to check for specific words example in fields such as Comments ; Description then choose CONTAINS Predicate.