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

  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.

    Skip to main content