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


Example 1

If the Names table includes the columns First_Name and Last_Name, which contain the following information:

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

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

then this statement:

SELECT TRIM (BOTH FROM Last_Name) || First_Name)

FROM Names ;

|| TRIM(BOTH FROM

returns the following string (note that the seven trailing blanks at the end of string Jones, and the eight trailing blanks at the end of string Mary are not included in the result):

'Jones, Mary'

Example 2

If the TRIM function is removed, the statement:

SELECT Last_Name || ', ' || First_Name

FROM Names;

returns trailing blanks in the string:

'Jones , Mary '

6-44

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

String Function: TRIM

As another example, assume column A is BYTE(4) and column B is VARBYTE(10).

If these columns contained the following values:

A_______ B________

78790000 43440000

68690000 3200

12550000 332200

then this expression:

SELECT TRIM (TRAILING FROM A) ||TRIM (TRAILING FROM B) FROM ...

returns:

78794344

686932

12553322

On a Japanese character site, TRIM operates as follows:

IF string_expr is this data type . . . THEN . . . AND the result is . . .
GRAPHIC any trailing graphic pad characters are removed VARGRAPHIC
CHAR only leading and/or trailing single byte space characters are removed VARCHAR TRIM takes into consideration multibyte character and Shift-Out/Shift-In characters when determining whether single byte character spaces can be removed.

The following tables detail the results of the TRIM function under each of the supported Kanji character sets.

Example 3

Teradata RDBMS for UNIX SQL Reference

6-45
SQL Expressions

String Function: TRIM

KanjiEBCDIC - TRIM (BOTH FROM string_expr)

KanjiEUC - TRIM (BOTH FROM string_expr)

string_expr Data Type Result
ABCAA Graphic (A iS a <zero-double byte> repreSented by 0x0000) ABC
MN<ABAA>dd Character (d iS a <Single byte-Space>, A iS a <double byte-Space>) MN<ABAA>
mn<ABAA> Character (A iS a<double byte-Space>) MN<ABAA>
d<AABA>d Character (d iS a <Single byte-Space>, A iS a <double byte-Space>) <AABA>

string_expr Data Type Result
ABCAA Graphic (A iS a <zero-double byte> repreSented by 0x0000) ABC
fd AB AAdd Character (d iS a <Single byte-Space> A iS a <double byte-Space>) fdABAA
DDfdABAADD Character (d iS a <Single byte-Space> A iS a <double byte-Space>) fdABAA

6-46

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

String Function: TRIM

KanjiShift-JIS - TRIM (BOTH FROM string_expr)

string_expr Data Type Result
ABCAA Graphic (A is a <zero-double byte> represented by 0x0000) ABC
fd AB AAdd Character (D is a <single byte-space> A is a <double byte-space) fdABAA
DDfdABAADD Character (D is a <single byte-space> A is a <double byte-space) fdABAA

Teradata RDBMS for UNIX SQL Reference

6-47
SQL Expressions

String Function: VARGRAPHIC

String Function: VARGRAPHIC

The VARGRAPHIC function is available only to users on a Japanese IntrOduCtiOn character supported site under the KanjiEBCDIC character set. Any

attempt to use it under another character set or on a non-Kanji site generates an error.

This function has the following form:

Form

- VARGRAPHIC---------( string_expr)------

HH01A035

VARGRAPHIC returns the vargraphic representation of the character data in string_expr.

All characters in the string are converted into one or more graphics Usage N°tes that are valid for the KanjiEBCDIC character set of the current

session. See Appendix J, “VARGRAPHIC Conversion.”

The following rules apply to the use of the VARGRAPHIC function:

• The string_expr must be character data.

Normally, if the string_expr contains any Shift-Out/Shift-In characters, they are not returned in the result string. They are required only to indicate the transition between single byte characters and multibyte characters.

Improperly placed Shift-Out/Shift-Ins are replaced by the illegal character for the character set of the session.

• Any single byte character in the string-expr is converted to its equivalent KanjiEBCDIC logical character.

Any single byte character which cannot be converted is replaced in the result with the appropriate illegal character for the character set of the session.

• Any multibyte character in the string-expr is returned unmodified.

• The result must satisfy the requirements of the VARGRAPHIC data type. Any result that exceeds the maximum length of a VARGRAPHIC data type generates an error.

6-48

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

String Function: VARGRAPHIC

Table 6-12 KanJiEBCDIC

The following table ShowS exampleS of converting KanjiEBCDIC Single byte characterS to double byte graphic data. The hexadecimal repreSentation of the StringS reSulting from VARGRAPHIC operationS are given in Appendix J, “VARGRAPHIC ConverSion”).
Previous << 1 .. 70 71 72 73 74 75 < 76 > 77 78 79 80 81 82 .. 241 >> Next