InstantSQL Search Patterns

Search Patterns

The TableName, SchemaName and ColumnName arguments in the SQL QUERY COLUMNS statement, the ProcedureName, SchemaName and ColumnName arguments in the SQL QUERY PROCEDURE-COLUMNS statement, the SchemaName and ProcedureName arguments in the SQL QUERY PROCEDURES statement, and the SchemaName and TableName arguments in the SQL QUERY TABLES statement accept search patterns, that is, strings that have search pattern characters. (Note: The SQL QUERY INDEXES statement does not allow search patterns for any of its arguments and the CatalogName argument is never a search pattern in any of the browse statements.)

The search pattern characters are:

An underscore (_), which represents any single character.

A percent sign (%), which represents any sequence of zero or more characters.

An escape character, which is driver-specific and is used to include underscores, percent signs, and the escape character as literal characters.

The escape character string (commonly a backslash character) is retrieved with the SQL DESCRIBE CONNECTION statement using an extended connection description group. The escape character string is returned in the sql-ConSearchPatternEsc data item. The escape character string must precede any underscore, percent sign, or escape character string in an argument that accepts search pattern strings to include that character literally instead of interpreting the character as a search pattern character. For example:

Search pattern	Description
%A%	All identifiers containing the letter A.
ABC_	All four-character identifiers starting with ABC.
ABC\_	The identifier ABC_, assuming the escape character is a backslash (\).
\\%	All identifiers starting with a backslash (\), assuming the escape character is a backslash.

Special care must be taken to escape search pattern characters in arguments that accept search patterns. This is particularly true for the underscore character, which is commonly used in identifiers. A common mistake in applications is to retrieve a value from one browse statement and pass that value to a search pattern argument in another browse statement. For example, suppose an application retrieves the table name "MY_TABLE" from the result set for SQL QUERY TABLES and passes this to SQL QUERY COLUMNS to retrieve a list of columns in the table named MY_TABLE. Instead of retrieving the columns for MY_TABLE, the application will retrieve the columns for all the tables that match the search pattern MY_TABLE, such as MY_TABLE, MY1TABLE, MY2TABLE, and so forth. To retrieve the columns for just MY_TABLE, the application must pass "MY\_TABLE" as the TableName argument to SQL QUERY COLUMNS. To see an example program that demonstrates how to convert a name returned from one InstantSQL browse statement into a search pattern with escapes on all the search pattern characters that exist in the name, click here Escape Example.

Passing a null pointer to a search pattern argument does not constrain the search for that argument; that is, a null pointer and the search pattern % (any characters) are equivalent. However, a zero-length search pattern — that is, a valid pointer to a string of length zero — matches only the empty string (""). InstantSQL always passes a pointer to a string derived from the provided COBOL argument data item for required arguments. If the COBOL argument data item value is spaces, a string of zero length will be passed. Therefore, the COBOL program should use "%" for a search pattern argument when a match to any name is desired. For search pattern arguments that are optional, omitting the argument (or specifying OMITTED for the argument) causes InstantSQL to pass a null pointer to ODBC, thus not constraining the search.