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 .. 37 38 39 40 41 42 < 43 > 44 45 46 47 48 49 .. 241 >> Next


Teradata RDBMS for UNIX SQL Reference

5-29
Data Definition

Specifying Case for Character Data

The following table summarizes case criteria and issues:

Case Function Specified or Not Usage Rules
CASESPECIFIC (CS) Character data is stored in the case in which it was entered unless UPPERCASE is also specified. In sorts or compares, lowercase characters are not converted to uppercase, and are not equal to uppercase characters. • If a column is defined as CASESPECIFIC, characters entered as ‘aaa’ are not equivalent to ‘AAA’ when used in a unique index. • Applications which most fully conform to ANSI should define columns and run in ANSI mode and use the UPPER function described later, to force case blind comparisons. An example is shown below.
NOT CASESPECIFIC In sorts or compares, lowercase characters are converted to uppercase; therefore, ‘aaa’ is equivalent to ‘AAA’. Note: Because the stored characters were not converted from lowercase to uppercase, data entered as ‘aaa’ is not equivalent to ‘AAA’ when used in a unique index.
Neither CS nor NOT CASESPECIFIC The session mode (ANSI or Teradata) determines the attribute assigned. • In ANSI mode, CASESPECIFIC is set. • In Teradata mode, NOT CASESPECIFIC is set.
UPPERCASE not specified, NOT CASESPECIFIC set AAA, aaa, AaA, and aAA are all equivalent as unique indexes.
UPPERCASE (UC) Character data is stored in uppercase, regardless of the case in which it was entered (any lowercase characters are converted to uppercase). Because of the conversion, if a column is defined as UPPERCASE, characters entered as ‘aaa’ are equivalent to ‘AAA’ when used in a unique index.

The following examples illustrate the options for specifying case for Examples 5 character data:

The Sex column is created using the UPPERCASE option:

Example 1 5

´ Sex CHAR UPPERCASE

If Sex data is entered in lowercase thus:

INSERT INTO Employee (Name, EmpNo, Sex . . .

VALUES ('Smith', 10021, 'f', . . .

SELECT Sex FROM Employee

it will be stored and returned in uppercase.

5-30

Teradata RDBMS for UNIX SQL Reference
Data Definition

Specifying Case for Character Data

Example 2

SELECT Name FROM Employee

WHERE Name(CS) = 'Leidner P' ;

The literal ‘Leidner P’ may default to CS or NOT CS, depending on the current mode, but since the type modifier of the comparison is CS, the comparison will be CS.

To ensure a case blind comparison, whether the current session is in Teradata or ANSI mode, the query would be:

SELECT Name FROM Employee

WHERE Name (NOT CS) = 'Leidner P' (NOT CS) ;

or alternatively, using ANSI compatible query:

SELECT Name FROM Employee

WHERE UPPER (Name) = 'LEIDNER P' ;

The following query returns a result only if a case specific comparison of the literal ‘Leidner P’ finds a match.

If used with the concatenation (||) operator, UC must be enclosed Example 3 5 in parentheses, and be placed immediately after the column name:

SELECT (City_name (UC)) || ', ' || State, Population

FROM World

WHERE Country = 'USA'

ORDER BY Population ;

CASESPECIFIC comparison and collation on mixed case data may Example 4 produce unintended results.

SELECT Last_Name FROM SalesReps ORDER BY Last_Name(CS) ;

may return one of the following, depending on the collation in effect for the session:

EBCDIC ASCII MULTINATIONAL
bart ACME ACME
fernandez ALBERT Albert
hill Albert ALBERT
Albert FARRAH bart
ACME Kimble FARRAH
ALBERT bart fernandez
FARRAH fernandez hill
Kimble hill Kimble

Collation can be defined as an attribute of the user via the CREATE USER or MODIFY USER statement, and can be set at the session level via the SET SESSION COLLATION statement.

Note: MULTINATIONAL collation is user defined, and can produce different results depending on how your system administrators have set up your system.

Teradata RDBMS for UNIX SQL Reference

5-31
Data Definition

Defining Case Sensitivity for Table Columns

Defining Case Sensitivity for Table Columns

The default for character columns defined under previous releases Introduction 5 has been NOT CASESPECIFIC, and columns were CASESPECIFIC

only by explicit definition.

All character data columns defined in ANSI mode will, by default, ANSI Mode be defined as case specific. This means that the columnar values 'aa'

and 'AA' are not treated as equals. An option of NOT CASESPECIFIC is implemented to allow columns to be case insensitive. If the SQL flagger option is on and the NOT CASESPECIFIC option is specified, it is flagged as an extension.

All character data columns defined in Teradata mode are, by Teradata MOde default, defined as NOT case specific.

Character data columns for tables created under prior releases continue to have the data type attribute set when columns were defined.

The UPPERCASE attribute is still supported, but flagged as nonANSI.

Observe that since simple Latin letters have canonical representation in all Teradata RDBMS character sets, including Kanji, the effect of the uppercase function is the same for each character set.
Previous << 1 .. 37 38 39 40 41 42 < 43 > 44 45 46 47 48 49 .. 241 >> Next