How To: Effectively Use the like Operator with the Query Attributes Task

Summary

The SQL LIKE operator is used to search for a specified pattern in a column. The LIKE operator can also be used with the wildcard characters, to simplify searches according to the desired expected result.

Note:
This article applies to retired ArcGIS versions 9.x. Later versions of ArcGIS may contain different functionality, as well as different names and locations for menus, commands and geoprocessing tools.

Procedure

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 typed into the Query Attributes task of the web application at runtime 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. The following example demonstrates an expression to convert both the attribute value and the user input to lower case before comparing the strings for query search:

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

Wildcards

Search queries can be simplified using wildcards, to match a fraction of the desired results. In Visual Studio, it is possible to incorporate wildcard characters directly into the query expression. Using a '%' (percentage) or '*' (asterisk) returns a result with every possible combination to replace the wildcards. The following examples demonstrate queries that searches any values that starts and ends with the letter a respectively:

where CustomerName like 'a%'

where CustomerName like '%a'

Alternatively, '_' (underscore) or '?' (question mark) can be used to query values to be replaced with a single letter, followed by a string. The following example demonstrates a query expression to match Catherine or Katherine:

where CustomerName like '_atherine'

Similar to the '%' and '*' wildcards, '_' and '?' can also be used at the end of a query expression. The following example demonstrates a query expression to match Mark or Marc:

where CustomerName like 'Mar_'

It is also possible to combine multiple wildcards together, to create a more precise query search. The following examples demonstrate a query expression that search for any values that have the letter 'r' in the second position and a query expression that search for any values that starts with 'a' and are at least three characters in length: