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 .. 66 67 68 69 70 71 < 72 > 73 74 75 76 77 78 .. 241 >> Next


Concatenation also operates on Kanji graphic data, as long as both strings are of the same data type. If you attempt to concatenate a graphic string with any other data type, the result is undefined and returns an error.

6-26

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

Character Strings: Concatenation Operator

The data type and content of the result are determined as follows.

IF this string . . . IS this data type . . . THEN the result is this data type . . .
both GRAPHIC GRAPHIC(n)
either VARGRAPHIC VARGRAPHIC(n)

The following tables show the results of concatenating string expressions under each of the Kanji character sets supported by the Teradata RDBMS1.

These examples assume that the string expressions adhere to the rules defined in Chapter 5, “Data Definition”.

KanjiEBCDIC -string_expr1 || string_expr2

string_expr1 string_expr2 Data Type Result
ABC D Graphic ABCD
ABCA D Graphic ABCAD
<ABC> <DEF>G Character <ABCDEF>G
<ABC> <> Character <ABC>
ABCA J Graphic and Character (illegal operation) Error
<ABC>a <DEF> Character <ABC>a<DEF>

KanjiEUC - string_expr1 || string_expr2

string_expr1 string_expr2 Data Type Result
ABC D Graphic ABCD
ABCA D Graphic ABCAD
ABCm DEFg Character ABCmDEFg
ss3A ss2B m ss3C Character ss3A ss2B m ss3C

1 < is Shift-Out, > is Shift-In, bold = double byte, G=graphic data string

Teradata RDBMS for UNIX SQL Reference

6-27
SQL Expressions

Character Strings: Concatenation Operator

KanjiShift-JIS -string_expr1 | string_expr2

Concatenation With TRIM

string_expr1 string_expr2 Data Type Result
ABCD B Graphic ABCDB
mnABC/ B Character mnABC/B
mnABC/ g Character mnABC/g

If the TRIM function is specified on CHAR or VARCHAR(n) data types, trailing blanks (leading and/or trailing blanks, when in ANSI mode) are suppressed in the concatenated string, according to which syntax is used.

For example, when the following information is used:

First_Name (CHAR(12)) has a value of 'Mary '

Last_Name (CHAR(12)) has a value of 'Jones '

the statement:

SELECT

TRIM (BOTH FROM Last_Name) || ', ' ||

TRIM (BOTH FROM First_Name)

FROM...

results in the string:

'Jones, Mary'

If the TRIM function is not specified, trailing blanks are returned in the result string.

SELECT

(Last_Name || ', ' || First_Name)

FROM ...

results in the string:

'Jones , Mary '

6-28

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

Character Strings: Concatenation Operator

The data types and results of concatenated strings with TRIM are listed below, where (n) is the sum of the lengths of all arguments:

• If any argument is null, the result is null.

• If the concatenation arguments are

• CHAR

• VARCHAR

• both

then the result of an argument on which TRIM is specified is VARCHAR(n), blank trimmed, with a possible zero length.

• If the concatenation arguments are

• BYTE

• VARBYTE

• both

then the result of an argument on which TRIM is specified is VARBYTE(n), binary 00 trimmed, with a possible zero length.

• If the concatenation arguments are

• GRAPHIC

• VARGRAPHIC

• both

then the result of an argument on which TRIM is specified is VARGRAPHIC(n), the graphic pad characters (binary 0000) trimmed, with a possible zero length.

• In ANSI mode, if LEADING or TRAILING or BOTH is not specified, then TRIM removes both leading and trailing pad characters (blank for CHAR, zero byte for BYTE, zero byte-pair for GRAPHIC).

• In Teradata mode, TRIM removes only trailing pad characters if LEADING or TRAILING or BOTH is not specified.

Teradata RDBMS for UNIX SQL Reference

6-29
SQL Expressions

Character String Expressions: String Functions

Character String Expressions: String Functions

The string functions include the following:

• SUBSTRING

• SUBSTR

• SUBSTR with KanjiEUC and KanjiShift-JIS

• MSUBSTR

• INDEX

• MINDEX

• TRIM

• VARGRAPHIC

• CHAR2HEXINT

• UPPER

Each function is described in the following passages.

The string functions are flagged as non-ANSI when the SQL flagger is enabled, with the following exceptions:

• TRIM (a subset of ANSI TRIM)

• SUBSTRING

SUBSTR is the Teradata RDBMS syntax for SUBSTRING. New applications should use SUBSTRING.

6-30

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

String Functions: SUBSTRING and SUBSTR

Form

Usage Notes

String Functions: SUBSTRING and SUBSTR

The SUBSTRING and SUBSTR functions have the form:

——SUBSTRING —(string_expr—FROM —n1 ^----------------,—)-,

Lfor n2 —I

— SUBSTR — (string_expr,n1 —³---1-)-------------------

Ln2—I

HH01A066

where:

Syntax Element . . . Is . . .
string_exp a string or string expression
n1 the starting position of the substring,
string_expr.
n2 the string_expr length.

SUBSTRING is the ANSI SQL syntax. Teradata syntax using SUBSTR is still supported for backward compatibility

Previous << 1 .. 66 67 68 69 70 71 < 72 > 73 74 75 76 77 78 .. 241 >> Next