Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 80 81 82 83 84 85 < 86 > 87 88 89 90 91 92 .. 241 >> Next


SELECT * FROM Project WHERE Proj_Id LIKE ANY

(SELECT Proj_Id FROM Charges

WHERE Proj_Id LIKE '%AP1%') ;

returns:

HH01A045

Project

Id

AP1-0003

AP1-0001

AP1-0002

Project Description___________

A/P Payable Batch System A/P Payable DB Design A/P Payable Online System

Received

_____Date

82/08/09

82/08/09

82/08/09

When this form is used, the subquery must select the same number of expressions as are in the expression list:

----(—Lexpr —I—)-,-1— LIKE quantifier ( subquery )-----------------

Lnot-I

HH01A046

For example:

(x,y) LIKE ALL (SELECT a,b FROM c)

is equivalent to:

(x LIKE c.a) AND (y LIKE c.b)

6-80

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

Logical Expressions: Japanese Character Site, LIKE Operator

Logical Expressions: Japanese Character Site, LIKE Operator

Introduction

On a Japanese character site, the LIKE operator can also operate on graphic data. The string being matched and the pattern string must be of the same data type.

Refer to Appendix G, “International and Japanese Character Support,” for detail about Japanese character supported sites.

Graphic Data

If the expressions reference graphic data, the following apply:

• All data comparisons are performed on logical characters.

• The <underscore double byte> represents one logical character.

• The <percent-sign double byte> represents a string of zero or more logical characters.

• Any other character represents a single occurrence of itself.

Character Data

If the expressions reference character data, the following apply:

• Unless the CASESPECIFIC qualifier is applied to one of the expressions, simple Latin letters in both the string being matched and the pattern string are converted to uppercase before comparison begins.

• If the <underscore> and <percent sign> symbols appear in the pattern string data, handling is as follows:

• An <underscore> refers to one single byte character; an <underscore double byte> refers to one logical character.

• A <percent sign>, either single byte character or multibyte character, refers to any number of characters of any type, either single byte character or multibyte character.

• Under the KanjiEBCDIC character set, the Shift-Out/Shift-In characters are treated only as an indication of a transition between an single byte character and an multibyte character.

• Any other character represents a single occurrence of itself, and can be converted to uppercase.

Teradata Mode

For mixed single byte character/multibyte character strings, 4 metacharacters are allowed:

• a single byte underscore matches a single byte character.

• a double byte underscore matches a single byte character or

multibyte character.

Teradata RDBMS for UNIX SQL Reference

6-81
SQL Expressions

Logical Expressions: Japanese Character Site, LIKE Operator

• a single byte percent matches any sequence of single byte characters or multibyte characters.

• a double byte percent matches any sequence of single byte characters or multibyte characters.

The operation of the LIKE pattern-string is the same as described above.

When using ANSI mode at Japanese character sites, note the ANSi Mode following usage rules:

For mixed single byte character/multibyte character strings, 4 metacharacters are allowed:

This metacharacter . . . Matches this sequence of characters . . .
single byte underscore a single single- or multibyte character.
double byte underscore a single single byte or multibyte character.
single byte percent any (single or multibyte).
double byte percent any (single or multibyte).

Note: The above allows 4 meta-characters. This differs semantically from ANSI SQL which specifies 2 meta-characters.

The following are rules for using graphic strings in LIKE operators Gmphic Strings at Japanese character sites:

For graphic strings, 2 meta-characters are allowed:

This metacharacter . . . Matches this sequence of characters . . .
double byte underscore a single
double byte percent any (multibyte characters).

Note: Single-byte underscore and percent do not exist in the graphic repertoire.

Any multibyte character can be the escape-character.

The following tables detail the results of applying the LIKE operator under the various Japanese character sets.

The results in the following examples do not consider the effects of a column defined as uppercase or case specific.

6-82

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

Logical Expressions: Japanese Character Site, LIKE Operator

Table 6-15

KanjiEBCDIC - (expr LIKE pattern-string)

expr1 pattern-string Data Type Return Value, ANSI mode Return Value, Teradata mode
ABC %C Graphic True True
ABC __C Graphic; represents an <underscore double byte> False False
MN<AB> %<B> Character True True
MN<AB>P <%B>% Character True True
xy<ABC>z %B% Character data and graphic data - improper operation Error Error
Previous << 1 .. 80 81 82 83 84 85 < 86 > 87 88 89 90 91 92 .. 241 >> Next