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 .. 69 70 71 72 73 74 < 75 > 76 77 78 79 80 81 .. 241 >> Next

if CASESPECIFIC is not specified for either expression any lowercase simple Latin letters are converted to uppercase before the operation begins. Any non-Latin single byte character, any multibyte character, and any byte indicating a transition between single byte character and multibyte character data are excluded from the conversion to uppercase function.
matching is in terms of logical characters; that is, single byte characters are matched against single byte characters, and multibyte characters are matched against multibyte characters. Therefore, representation of the logical character must be identical in both expressions in order for a match to occur. the position of the result, if not zero, is reported as follows:
(Note that under a KanjiEBCDIC character set, although the Shift-Out/Shift-In characters are included in the offset count, they are not matched; they are treated only as an indication of a transition between a single byte character and an multibyte character.)

Teradata RDBMS for UNIX SQL Reference

6-39
SQL Expressions

String Function: INDEX

IF both string_expr are this data type . .

AND .

THEN .

Under this character set . . . The result is . . .
KanjiEBCDIC the byte corresponding to the first byte of the logical character offset (including Shift-Out/Shift-In in the offset count) within string_expr
all other Kanji the logical character offset within string_expr

6-40

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

String Function: INDEX

The following tables detail the output from the INDEX function for each type of Kanji character set.

Table 6-8 string_expr1 string_expr2 Data Type Result
KanJiEBCDIC - ABC INDEX(string_expr1,string_expr2) ABC C Graphic 3
ABC C A Graphic 0
MN<AB> > B < Character 6
MN<AB> < A V Character 4
MN<AB>P P Character 9
MIN<AB>P > B < Character 7

Table 6-9 string_expr1 string_expr2 Data Type Result
KanjiEUC - INDEX(string_expr1, string_expr2) CS1 _UAIA A Graphic 6
A CO -Q 03 A3 3 Character 3
a b SS2B SS2B Character 3
CS1_DATA A Character 6
a b SS2D SS3E SS2F SS2F Character 5
a b C SS2D SS3E SS2F SS2F Character 6
CS1_DmATA A Character 7

Table 6-10 string_expr1 string_expr2 Data Type Result
KanJiShift-JIS - INDEX(string_expr1, string_expr2) ABCD B Graphic 2
mnABCl B Character 4
mnABCl I Character 6

Teradata RDBMS for UNIX SQL Reference

6-41
SQL Expressions

String Function: MINDEX

String Function: MINDEX

Under the KanjiEBCDIC character set, MINDEX reports the logical Introduction character offset of the position where string_expr2 was found

within string_expr1.

The MINDEX function is expressed in the following form:

Form

----MiNDEX-(string_expr1, string_expr2)-------

HH01A032

The following table details the output from the MINDEX function.

Table 6-11

KanjiEBCDIC - MINDEX(string_expr1, string_expr2)

string_expr1 string_expr2 Data Type Result
ABC C Graphic 3
ABC CA Graphic 0
MN<AB> > B < Character 4
MN<AB> < A V Character 3
MN<AB>P P Character 5
MIN<AB>P > B < Character 5

6-42

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

String Function: TRIM

String Function: TRIM

Introduction

The TRIM function takes a single argument, of character or byte data, and trims blank characters or bytes with value zero only in the argument result.

Form

The syntax is:

-TRIM-----(-

- BOTH FROM

LEADING FROM -

TRAILING FROM-

- string_expr)

HH01A034

where:
Syntax Element . . . Trims . . .
BOTH FROM both trailing and leading blanks.
TRAILING FROM only trailing blanks.
LEADING FROM only leading blanks.
string_expr the following data types for string_expr. BYTE and VARBYTE data types, in which case null bytes are trimmed. CHAR and VARCHAR, in which case blanks and SBC/MBC spaces are trimmed. GRAPHIC and VARGRAPHIC, in which case double byte nulls or zeroes are trimmed.

The form TRIM string_expr is dependent on whether the session is in Teradata or ANSI mode:

In this mode . . . TRIM is equivalent to . . .
ANSI TRIM (BOTH FROM string_expr)
Teradata TRIM (TRAILING FROM string_expr)

Returns From a TRIM Function

Possible returns from TRIM are:

Teradata RDBMS for UNIX SQL Reference

6-43
SQL Expressions

String Function: TRIM

WHEN TRIM operates on . . . THEN the result is . . .
a null argument null.
a numeric argument first converted to a character string using the default format of the numeric value.
the return from a CHAR or VARCHAR argument VARCHAR, blank trimmed, with a possible zero length.
the return from a BYTE or VARBYTE arguments VARBYTE, binary 00 trimmed, with a possible zero length.
the return from a GRAPHIC argument VARGRAPHIC, binary 0000 trimmed with a possible zero length.

Using TRIM With Concatenation Operator

The TRIM function is typically used with the concatenation operator to remove trailing blanks or trailing bytes containing binary 00 from the concatenated string.
Previous << 1 .. 69 70 71 72 73 74 < 75 > 76 77 78 79 80 81 .. 241 >> Next