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

If a numeric expression is used as the first argument, it is converted to a CHAR(n) string, where n and the format are determined by the default format for the numeric type.

Note: SUBSTR, SUBSTRING, and MSUBSTR perform in the same manner except for the one case when SUBSTR is used for KanjiEBCDIC in Teradata mode. Refer to the following section, “Japanese Characters and Graphic Data”.

Teradata RDBMS for UNIX SQL Reference

6-31
SQL Expressions

String Functions: SUBSTRING and SUBSTR

The behavior of SUBSTRING and SUBSTR is explained by the SUBSTRING and SUBSTR following rules:

Behavior

IF n2 is . . . AND IF . . . THEN . . . ELSE . . . ELSE IF. . .
specified n2 < 0 an error returns. This is the only case for which an error returns.
n1 < 1 set n2=n2+n1 - 1 WHEN n2 <= 0, a string with zero length returns. THEN set n1=1 IF n1 > string_length a string with zero length returns n1 + n2 -1> string_length THEN set n2 = string_length - n1 +1 WHEN n2 <=0 a string with zero length returns
not specified n1 < 1 n1 > string_length the source string returns a string with zero length returns a substring starting at n1 and ending with the last character of string_expr returns.

The possibly adjusted n1 and n2 now identify the substring to be returned.

The substring returned starts at position n1 in the string_expr and extends for n2 characters or to the last character in the source string if there are not n2 characters available.

Note: In prior releases using SUBSTR, an error was reported if n1 was zero, negative, or more than one position beyond the length of the string_expr.

Suppose SN is a CHAR(15) field of Serial IDs for Automobiles and Example1 positions 3 to 5 represent the country of origin as three letters.

For example:

12JAP3764-35421

37USA9873-26189

11KOR1221-13145

6-32

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

String Functions: SUBSTRING and SUBSTR

To search for SNs of cars made in the USA:

SELECT MAKE, SN FROM Autos

WHERE SUBSTRING (SN FROM 3 FOR 3) = 'USA'

Example 2

Example 3

If instead of SN, we wanted the last five characters, which represent manufacturing sequence number, another substring can be accessed.

SELECT MAKE, SUBSTRING (SN FROM 11) AS SEQUENCE FROM Autos WHERE SUBSTRING (SN FROM 3 FOR 3) = 'USA'

Suppose NAMEADDRESS is a VARCHAR(120) field, and the application used positions 1 to 30 for name, starting address at 31. To return address only, but limit the number of characters returned to 50 use:

SUBSTRING (NAMEADDRESS FROM 31 FOR 50)

This returns an address of up to 50 characters.

Example 4

The following example shows various returns from SUBSTR—A SELECT expression requesting substrings from a character field returns the characters in positions 1 through 4 for every row:

SELECT SUBSTRING (Jobtitle FROM 1 FOR 4)

FROM EMPLOYEE ;

The result is as follows.

SUBSTRING(JobTitle FROM 1 FOR 4)

Tech

Cont

Sale

Secr

Test

Teradata RDBMS for UNIX SQL Reference

6-33
SQL Expressions

Japanese Characters and Graphic Data

Introduction

SUBSTR in Teradata Mode

SUBSTR in ANSI Mode

Returning Part of a Character or Graphic Expression

Japanese Characters and Graphic Data

The start position, n1, and length, n2, are parameters that pertain to logical characters.

For character sets with a fixed number of bytes per character, the operations are straightforward in terms of the arithmetic expressions shown. this applies to ASCII and GRAPHIC data types.

For some Japanese character data types, the physical operation of finding and adjusting the start_position, n1 and length, n2, is complicated by the fact that the intent is to count logical characters, requiring a scan of the characters in the source string to relate logical to physical.

If the session is in Teradata Mode, the SUBSTR function operates without change. Certain multibyte character sets are handled as though they were single byte characters. This is described as physical mode. The multibyte character sets are:

• KatakanaEBCDIC

• KanjiEBCDIC5035_0I

• KanjiEBCDIC5026_0I

If the session is in ANSI Mode, it is handled like MSUBSTR. All MBC character sets are handled in terms of logical characters.

• For the syntax: MSUBSTR ( string, start [,length]) there is no change.

• For the syntax: SUBSTRING (string FROM start [FOR length]), the behavior is the same as for MSUBSTR, that is, all multibyte character sets are handled in terms of logical characters.

On a Japanese character site, the SUBSTR function can return part of

a character or graphic expression:
IF string_expr is this data type . . . THEN the result is this type of substring . . .
character character
graphic graphic

6-34

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

Japanese Characters and Graphic Data

Table 6-4

KanjiEBCDIC - SUBSTR(string_expr, n1 [, n2])

SUBSTRING with KanjiEUC and KanjiShift-JIS
Previous << 1 .. 67 68 69 70 71 72 < 73 > 74 75 76 77 78 79 .. 241 >> Next