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


DeptNo Count

? 2

100 4

300 3

500 7

600 4

700 3

Teradata RDBMS for UNIX SQL Reference

6-21
SQL Expressions

Aggregate Operators

Example 5

To determine the number of departments in the Employee table, use COUNT (DISTINCT) as a prefix operator, as follows:

SELECT COUNT (DISTINCT DeptNo) FROM Employee ;

The system responds with:

Count(Distinct(DeptNo))

5

6-22

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

Character String Expressions

Character String Expressions

Teradata SQL provides the following to format and operate on character string arguments:

• Concatenation operator

• String functions

Also, comparison operators and the LIKE partial-string operator can be used with CHAR, VARCHAR, GRAPHIC, and VARGRAPHIC string arguments.

Character string expressions are flagged as non Entry Level ANSI SQL, when the SQL flagger is enabled.

Teradata RDBMS for UNIX SQL Reference

6-23
SQL Expressions

Character Strings: Concatenation Operator

Introduction

Argument String Data Types

Character Strings: Concatenation Operator

The concatenation operator has the form:

---string_expr —p| I -ł— string_expr -

It

string_expr -

FF07A076

where string_expr is a string or string expression. The result of a concatenation operation is a string formed by concatenating the arguments in a left-to-right direction.

Note: Exclamation marks (!!), previously acceptable in Teradata RDBMS as concatenation operators, are non-ANSI.

Solid (||) and broken vertical bars are acceptable by Teradata RDBMS and ANSI as concatenation operators. Note that on some keyboards, the vertical bar is shown as a broken vertical bar but prints as a solid vertical bar.

The rules of data type for the argument strings are as follows:

• If any argument is a numeric data type, the value is first converted to a character string using the format for the numeric value.

See Chapter 5, “Data Definition,” in the section “Numeric to Numeric” for details about the caution when implicitly converting numeric to character types.

• If any argument is BYTE or VARBYTE, all other arguments must also be of a byte data type.

6-24

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

Character Strings: Concatenation Operator

Data types of the concatenated results are shown following, where Data Types of (n) is the sum of the lengths of all arguments:

Concatenated Results

BYTE, VARBYTE, GRAPHIC, and VARGRAPHIC are not ANSI compliant. If you are in ANSI mode and the flagger is enabled you are flagged when you use these data types.

IF this argument . . . IS this data type . . . THEN the result is . . .
any null null.
VARBYTE VARBYTE(n).
VARCHAR VARCHAR(n)
VARGRAPHIC VARGRAPHIC(n)
all BYTE BYTE(n)
CHAR CHAR(n)
GRAPHIC GRAPHIC(n)
either argument of concatenation CASESPECIFIC CASESPECIFIC

The default heading of the concatenation result is the name or title of the left operand, then || followed by the name or title of the right operand.

TstTblA defines Col1 as BYTE(2) and Col2 as VARBYTE(10). Also, Concatenation °pei'atOr: hexadecimal constants ’4142’XB and ’7A7B7C’XB are inserted, into

Examples Col1 and Col2.

Constants, spaces, and the TITLE phrase can be included in the Example 1 6 operation definition to format the result heading and improve

readability.

For example, the following definition:

SELECT ('Sex ' || Sex ||

', Marital Status ' || MStat)(TITLE ' ')

FROM Employee ;

returns side titles, evenly spaced result strings, and a blank heading:

Sex M, Marital Status S
Sex F, Marital Status M
Sex M, Marital Status M
Sex F, Marital Status M
Sex F, Marital Status M
Sex M, Marital Status M
Sex F, Marital Status W

Teradata RDBMS for UNIX SQL Reference

6-25
SQL Expressions

Character Strings: Concatenation Operator

Example 2

Example 3

Example 4 (Concatenating Byte Strings)

Japanese Character Site Features

A Names table contains last and first names columns, defined as VARCHAR, thus:

Lname__________Fname

Ryan Loretta

Villegas Arnando

Kanieski Carol

Brown Alan

Use string concatenation and a space separator to combine first and last names:

SELECT fname ||' '|| lname FROM names

ORDER BY lname ;

The result is:

((Fname||' ')||Lname)

Alan Brown Carol Kanieski Loretta Ryan Arnando Villegas

Change the SELECT and the separator to obtain Lname, Fname:

SELECT lnamell','llfname FROM names ORDER BY lname;

The result is:

((Lname||,)||Fname)

Brown, Alan Kanieski, Carol Ryan, Loretta Villegas, Arnando

The following example concatenates byte strings.

SELECT Col2 || Coll FROM TstTblA ;

returns:

(Col2||Col1)

7A7B7C4142

With the exception of KanjiEBCDIC, concatenation on a Japanese character site acts as described above. Under KanjiEBCDIC, any adjacent shift-out and shift-in characters (> <) within the resulting expression are removed. In this case, the result string is padded as necessary with trailing <single-byte space> characters.
Previous << 1 .. 65 66 67 68 69 70 < 71 > 72 73 74 75 76 77 .. 241 >> Next