English

How To: Effectively use the LIKE operator with the Query Attributes task

Summary

The following procedure provides some tips to effectively use the LIKE operator in query expressions.

Procedure

When building expressions for the Query Attributes task, the LIKE operator can be used to make string comparisons. The following tips help in effectively using the LIKE operator in any expressions.

  • Case sensitivity

    The LIKE operator is case sensitive. Thus, the text string that is typed into the Query Attributes task of the Web application at run time must match the case of the actual attribute value being matched.

    Some databases support the UPPER and LOWER SQL functions. Either of these can be used to convert both the attribute value and the user input to the same case. When configuring the Query Attributes task in Visual Studio, a custom 'where' clause can be specified to be used as the expression. For example, an expression like this can be written to search a Name field for the user-specified value:

    lower(NAME) like lower({'0'})

    In this expression, both the attribute value and the user input value will be converted to lower case before string comparison.
  • Wildcards

    The LIKE operator supports wildcards. The end user of the Web application can use wildcard characters when typing in the search string they want to match. Depending on the database that the data is stored in, using a % (percent sign) or * (asterisk) means that anything is acceptable in its place: one character, a hundred characters, or no character. For example, the end user can type in:

    a%

    to find all strings that begin with the letter 'a'. Alternatively, _ (underscore) or ? (question mark) will match a single character. For example, the end user can type in:

    _atherine

    to match both Catherine and Katherine.

    In Visual Studio, it is possible to incorporate wildcard characters directly into the query expression. This eliminates the need for end users to type them in. This can be done by defining a custom expression for the query. For example, an expression, such as the following, could be entered:

    NAME like '{0}%'

    where the percent sign is automatically appended to any user input.