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


Table 6-5

KanjiEUC - SUBSTR(string_expr, start [, len])

Table 6-6

KanjiShift-JIS - SUBSTR(string_expr, start [, len])

string_expr n1 n2 Data Type Result
ABCD 2 2 Graphic BC
MN<ABC>P 2 Character N<ABC>P
MN<ABC>P 3 8 Character <ABC>
MN<ABC>P 4 Character (improperly formatted) ABC>P

Under KanjiEUC and KanjiShift-JIS, if string_expr is character data, the SUBSTRING (SUBSTR and MSUBSTR) function expects mixed single byte character/multibyte character strings and operates on logical characters that are valid for the character set of the session. In this case, n1 is a positive integer pointing to the first character of the result and n2 is in terms of logical characters.

If string_expr is graphic data, a character is a logical character and n1 and n2 are in terms of logical characters.

The following tables detail the results of the SUBSTRING (SUBSTR and MSUBSTR) function for the KanjiEUC and KanjiShift-JIS character sets.

string_expr n1 n2 Data Type Result
ABCD 2 Graphic BCD
A ss2B CD 2 2 Character ss2B C
ss3A ss2B ss3C ss2D 2 2 Character ss2B ss3C

string_expr n1 n2 Data Type Result
ABCD 2 Graphic BCD
mnABC/ 6 1 Character /
mnABC/ 4 Character BC/

Teradata RDBMS for UNIX SQL Reference

6-35
SQL Expressions

String Function: MSUBSTR

Introduction

Form

Table 6-7

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

String Function: MSUBSTR

This function operates only on logical characters. It performs in the same manner as the SUBSTR function, except that n1 and n2 must be specified in terms of logical characters, and the Shift-Out/Shift-In characters are added as necessary to achieve a properly formatted substring.

MSUBSTR has the following form:

----MSUBSTR------(string_expr, , ,)

L, n2I

HH01A030

MSUBSTR behaves the same as SUBSTRING.

The following table details the results of the MSUBSTR function for the KanjiEBCDIC character set.

string_expr start len Data Type Result
ABCD 2 2 Graphic BC
MN<ABC>P 2 Character N<ABC>P
MN<ABCD>P 4 2 Character <BC>
MN<ABC>P 4 Character <BC>P

6-36

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

String Function: INDEX

String Function: INDEX

Introduction

INDEX returns the position in the string where the substring starts. If the substring is not found in the string, the result is zero.

If an argument is a numeric value, the value is converted to a character string using the default format of the numeric type.

Form

The INDEX function has the form:

INDEX---------(string_expr-, string_expr)---------

HH01A031

where the first argument represents a string to be searched and the second represents a possible substring. The result of INDEX is always INTEGER.

Example 1

Examples of INDEX expressions and their results follow:

Expression

INDEX('catalog','dog'

INDEX('41424344'XB,'43'XB)

Result INDEX('catalog','log'

0

3

Example 2

In this example, INDEX is applied to the substring (space) in the value strings in the Name column of the Employee table:

SELECT name FROM employee

WHERE INDEX(name, ' ') > 6 ;

The system examines the Name field and returns all values where the defined space appears in a character position beyond the sixth (i.e., character position seven or further). The result is a list in which each last name is six characters or longer.

Example 3

In the following example, display a list of projects in which the word Batch appears in the project description, and list the starting position of the word:

SELECT proj_id, INDEX(description, 'Batch') FROM project

WHERE INDEX(description, 'Batch') > 0 ;

5

Teradata RDBMS for UNIX SQL Reference

6-37
SQL Expressions

String Function: INDEX

Example 4

The system returns:

Project

______Id Index ( Project Description, 'Batch')

OE2-0003 5

AP2-0003 13

OE1-0003 5

AP1-0003 13

AR1-0003 10

AR2-0003 10

A somewhat more complex construction employing SUBSTRING, INDEX, and concatenation may be more useful. Transpose the form of the names selected from the Employee table and change the punctuation with:

SELECT empno,

SUBSTRING(name FROM INDEX(name,' ')+1 FOR 1)||'. '|| SUBSTRING(name FROM 1 FOR INDEX(name, ' ')-1)

(TITLE 'Emp Name')

FROM employee ;

The system returns:

EmpNo Emp Name
10021 T. Smith
10007 J. Aguilar
10018 S. Russell
10011 M. Chin
10019 P. Newman

6-38

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

String Function: INDEX

On a Japanese character site, the INDEX function can be applied to

Japanese Character graphic and character data as follows:

Features

IF both string_expr are this data type . . . AND .. . THEN . . .
graphic matching is in terms of logical characters; that is, multibyte characters are matched against multibyte characters the position of the result, if not zero, is reported as the logical character offset within string_expr.
character if CASESPECIFIC is specified, simple Latin letters are considered matched only if they are the same letters and the same case.
Previous << 1 .. 68 69 70 71 72 73 < 74 > 75 76 77 78 79 80 .. 241 >> Next