15.00 - Using Wildcard Characters in Names - Teradata Database

Teradata Database Utilities

Product
Teradata Database
Release Number
15.00
Content Type
Configuration
Publication ID
B035-1102-015K
Language
English (United States)
Last Update
2018-09-25

Using Wildcard Characters in Names

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)
  • 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 SQL Fundamentals. For information on syntax error messages, see “Syntax Error Messages” on page 54.

    Example

    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  

    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:

  • AZ
  • az
  • 09
  • _ (low line or underscore)
  • $ (dollar sign)
  • # (number sign)
  • You cannot use digits 09 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” on page 54.

    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” on page 54.

    Wildcard syntax can include characters that might not have any matching object names in the system. However, at least one character in the wildcard syntax must have at least one matching object.

    Consider the following:

  • 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.
  • If the system has no object name that matches any of the specified wildcard characters, CheckTable issues a warning message.
  • 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;

    The command is valid because one of the characters specified in the wildcard syntax has a matching character at the position where the wildcard syntax is specified in one object name. CheckTable checks all the tables in the database db1 and ignores the remaining wildcard characters.

    Example 3: Assume a system that does not have databases db1, db2, and db3. Type the following:

    CHECK db[1-3]AT LEVEL ONE;

    The following warning appears:

    >>>> WARNING: Database 'DB[1-3]' does not exist.

    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.