Using Wildcard Characters in Names - Advanced SQL Engine - Teradata Database

Database Utilities

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
June 2020
Language
English (United States)
Last Update
2020-06-15
dita:mapPath
boh1556732696163.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1102
lifecycle
previous
Product Category
Teradata Vantage™

Use wildcard characters % and ? to specify a pattern for database names or table names.

The following table shows how CheckTable interprets wildcard characters.

Wildcard character … Matches any …
% (percent sign) string of characters of any length, including the Null string.
? (question mark) single character.

You can use wildcard characters in any combination. However, you cannot use wildcard characters in hexadecimal form.

The following table shows the use of wildcard characters in names.

Wildcard character … Matches any …
% database name.
%.% table name.
%database% database name containing the string: database.
SalesDB% database name beginning with the following: SalesDB.

Wildcard Syntax

CheckTable supports the use of wildcard syntax to represent a list of possible characters at a particular position in the names of databases or tables. Use the wildcard syntax to specify lists of tables and databases you want CheckTable to check or not check. The wildcard syntax begins with a left square bracket ([) and ends with a right square bracket (]).

The following figure shows the syntax for a database name or a table name.



Syntax element … Specifies …
Starting char one of the following:
  • alphabet (lowercase or uppercase)
  • ? (question mark)
  • % (percent sign)
Start range char one of the following:
  • alphabet (lowercase or uppercase)
  • $ (dollar sign)
  • _ (low line or underscore)
  • # (number sign)
Alphabet range two alphabets separated by a hyphen.

The range can be in ascending or descending order. Both the alphabets should be the same type, either uppercase or lowercase.

Remaining chars one of the following:
  • alphabet (uppercase or lowercase)
  • digit between 0 and 9
  • ? (question mark)
  • % (percent sign)
  • $ (dollar sign)
  • _ (low line or underscore)
  • # (number sign or hash mark)
Digit any digit between 0 and 9.
Hyphen range two alphabets or two digits separated by a hyphen.

The range can be in ascending or descending order. Both the characters should be the same type, uppercase or lowercase alphabet or digit.

Char one of the following:
  • alphabet (uppercase or lowercase)
  • digit between 0 and 9
  • special character
  • Kanji character

Other special characters can appear in table or database names but not in wildcard syntax. If any syntax error occurs in the wildcard syntax, then CheckTable aborts, and an error message appears.

For rules regarding use of Kanji and other Japanese characters in names, see “Basic SQL Syntax and Lexicon” in Teradata Vantage™ - SQL Fundamentals, B035-1141. For information on syntax error messages, see Syntax Error Messages.

Example: Using wildcard syntax

CHECK DB[15] AT LEVEL ONE;

The wildcard syntax defines two possible values (1 and 5) for the third character in the database name. CheckTable checks all the tables in databases DB1 and DB5 at level one.

Example: Using multiple wildcard syntax

CHECK D[BD]1.T[123] AT LEVEL ONE;

You can use the wildcard syntax in any place in the database name or table name. CheckTable checks tables T1, T2, T3 in databases DB1 and DD1.

Rules for Using Wildcard Syntax

The following rules apply to the use of wildcard syntax in the CHECK command. Assume that the databases and tables in the examples exist in the system, unless stated otherwise.

Rule Example
You can specify the following valid ASCII characters in the wildcard syntax:
  • A … Z
  • a … z
  • 0 … 9
  • _ (low line or underscore)
  • $ (dollar sign)
  • # (number sign)

You cannot use digits 0 … 9 as wildcards to describe the first character in the name.

Example 1: The following is a valid command:
CHECK db1.t[#af_2r]1 AT LEVEL ONE;

Example 2: The following is not a valid command:

CHECK db[#,kA-d159]xy AT LEVEL ONE;

The above command results in a syntax error because the wildcards specified for database name include the non-valid comma (,). For information on syntax error messages, see Syntax Error Messages.

You must specify the wildcard characters within square brackets. The wildcard syntax begins with a left square bracket ([) and ends with a right square bracket (]). Example 1:Databases db1, db2, db3, db4, and db5 exist, and you want only the tables in db1 and db5 checked. Type the following:
CHECK db[15] AT LEVEL ONE;

CheckTable checks all the tables in databases db1 and db5 at level one. The wildcard syntax defines two possible values (1 and 5) for the third character in the database name.

Example 2:Databases db1, dc1, dd1, and so on exist, and each database contains tables t1, t2, t3, and so on. Using the wildcard syntax in any place in the name, type the following:

CHECK d[bd]1.t[123] AT LEVEL ONE;

CheckTable checks tables t1, t2, t3 in databases db1 and dd1.

Example 3: To specify wildcard syntax in multiple places in a name, type the following:

CHECK db[12][pq] AT LEVEL TWO;

CheckTable checks databases db1p, db2p, db1q, and db2q at level two. The wildcard syntax defines the possible values for the third and fourth characters of the database name.

You cannot specify the special characters % and ? within wildcard syntax. However, you can use the special characters % and ? with any valid wildcard syntax. Example 1:Databases dba1, dba2, db11 and db12 exist, and you want to check databases dba1, dba2, db11, and db12. Type the following:
CHECK db[a1]? at level one;

This command is valid, because the ‘?’ is outside the wildcard syntax.

Example 2: The following is not a valid command, because the ‘?’ is not allowed in wildcard syntax.

CHECK db[a1?] at level one;
You can use wildcard syntax to specify the names or lists of the databases and tables to check and the list of databases or tables not to check. Example 1:Databases db1, db2, db3 and db4 exist, and you type the following:
CHECK db% exclude db[34] at level one;

Databases db1 and db2 are checked.

Example 2: Databases db1, db2, db3 and db4 exist, and all these databases have tables t1, t2, t3 and t4. You type the following:

CHECK db[23] exclude t[14] at level one;

CheckTable checks tables t2 and t3 in databases db2 and db3.

You can use wildcard syntax to specify a range of characters by separating two characters with a hyphen (-). For example, C and J separated by the hyphen (C-J) represent any characters lexically between C and J inclusive.
  • The two characters should be of the same type: uppercase, lowercase, or digit.
  • The two characters can be in ascending or descending lexical order. For example, [A-D] and [D-A] both specify the same range of characters: A through D inclusive.
Example 1:
CHECK db1.t[1-35] AT LEVEL ONE;

CheckTable checks the tables t1, t2, t3, and t5 in database db1 at level one. 1-3 is considered a range, and 5 is an additional value.

Example 2:

CHECK db[a-5] AT LEVEL ONE;

The check does not take place. CheckTable reports a syntax error because the range specified in dbname is invalid. For information on syntax error messages, see Syntax Error Messages.

Wildcard syntax can include characters that might not have any matching object names in the system.

If the syntax contains some characters that do not have a match at the position specified in any object names in the system, CheckTable checks (or excludes from checking) all the objects whose names match the specified wildcards. CheckTable also ignores the characters that do not have any matching objects. This is true of any number of wildcards.

Example 1: Assume a system contains only databases db1 and db5 but not db2, db3, and so on. Type the following:
CHECK db[125] AT LEVEL ONE;

CheckTable checks all the tables in databases db1 and db5 at level one. Since database db2 does not exist, CheckTable ignores character 2 in the wildcard syntax.

Example 2: Assume a system contains the database db1 but not db2, db3, or db4. Type the following:

CHECK db[1-4] AT LEVEL ONE;

CheckTable checks all the tables in the database db1 and ignores the remaining wildcard characters.

Multiple occurrences of the same character in the wildcard syntax are valid. If you repeat the same character in the syntax for the same position, then CheckTable recognizes the first occurrence and ignores the repeated instances. Example 1: In the following command, character b is repeated in the same position.
CHECK d[abb]1 AT LEVEL ONE;

CheckTable checks all tables in the databases da1 and db1 at level one and ignores the second instance of character b. No warning appears.

Example 2: In the following command, character 3 is specified as part of the hyphen range 1-5 and is repeated separately in the same position.

CHECK db[1-53] AT LEVEL ONE;

CheckTable checks all tables in the databases db1, db2, db3, db4, and db5 at level one. CheckTable ignores the repeated character 3.

The wildcard syntax does not apply when enclosed between apostrophes or double quotation marks. In the following command, character p is a wildcard enclosed in double quotation marks.
CHECK db1."[p]" AT LEVEL ONE;

CheckTable ignores the square brackets and checks only table “[p]”, if it exists in database db1. If table “[p]” does not exist in db1, then a warning appears.