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 .. 79 80 81 82 83 84 < 85 > 86 87 88 89 90 91 .. 241 >> Next


appends trailing blanks to pattern strings shorter than 5 characters long.

6-76

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

Logical Expressions: LIKE Partial-String Operator

• To retrieve the row in all cases, apply the TRIM function to the pattern string (TRIM (TRAILING FROM :pattern) ), or the macro parameter can be defined as VARCHAR.

These two methods do not always return the same results.TRIM removes blanks, while the VARCHAR method maintains the data pattern exactly as entered.

The following example uses a LIKE partial-string operator to select Example 1 a list of employees whose job title contains the string “Pres”:

SELECT Name, DeptNo, JobTitle FROM Employee

WHERE JobTitle LIKE '%Pres%' ;

The form %string% requires the Teradata RDBMS to examine much of each string x. If x is long and there are many rows in the table, the search for qualifying rows may take a long time.

The result returned is:

Name_________ DeptNo JobTitle__________

Watson L 500 Vice President

Phan A 300 Vice President

Russell S 300 President

The next example selects a list of all employees whose last name Example 2 begins with the letter P.

SELECT Name FROM Employee WHERE Name LIKE 'P%';

The result returned is:

Name Phan A Peterson J

The % and _ characters are used to select a list of employees with Example 3 6 the letter A as the second letter in the last name. The length of the

string returned may be two or more characters.

SELECT Name FROM Employee WHERE Name LIKE '_a%';

returns the result:

Name________

Marston A Watson L Carter J

If the preceding example used _a_, the search would be for a three-character string with the letter a as the second character.

Teradata RDBMS for UNIX SQL Reference

6-77
SQL Expressions

Logical Expressions: LIKE Partial-String Operator

Because none of the names in the Employee table fit this description, no rows would be returned.

Both leading and trailing blanks in a pattern are significant to the matching rules.

Example 4:

LIKE ’bbZ%’ (where b = blank) locates only those fields that start with two blanks followed by Z.

quantifier

Teradata SQL recognizes the quantifiers ANY (or SOME) and ALL. A quantifier allows one or more expressions to be compared with one or more values.

For example, using the following form:

expr -

¯-------Quantifier------(—I—

LIKE — quantifier —(—I— pattern_string J-)

HH01A044

IF you specify this quantifier . . . THEN the search condition is satisfied if expr LIKE pattern-string, ... is true for . . .
ALL every string in the list.
ANY any string in the list.
The ALL quantifier is the logical statement FOR ".

The ANY quantifier is the logical statement FOR $. The following table restates this.

This Expression . . . Is Equivalent to . . .
x LIKE ALL ('A%', %B','%C%') x LIKE x LIKE E K I L x ND ' %' %C A% B' AND
x LIKE ANY ('A%', %B','%C%') x LIKE LIKE '% 'A%' OR x LIKE '%I C%' ' OR x

The following statement selects from the employee table the row of any employee whose job title includes the characters “Pres” or begins with the characters “Man”:

SELECT * FROM Employee WHERE JobTitle LIKE ANY ('%Pres%', 'Man%');

6-78

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

Logical Expressions: LIKE Partial-String Operator

The reSult of thiS Statement iS:

For the following formS:

EmpNo Name DeptNo JobTitle Salary
10021 Smith T 700 Manager 45, , 000.00
10008 Phan A 300 Vice Pres 55, , 000.00
10007 Aguilar J 600 Manager 45, , 000.00
10018 Russell S 300 President 65, , 000.00
10012 Watson L 500 Vice Pres 56, , 000.00

expr

LIKE-

quantifier

3

( subquery )

expr

LIKE

quantifier

( subquery )

HH01A059

If the ALL or ANY/SOME quantifier is specified, the subquery may return none, one, or Several rowS.

If, however, a quantifier iS not uSed, then the Subquery muSt return either no value, or a Single value. That iS:

This Expression . . . Is True WHEN expre matches. . .
expr LIKE (subquery) the single value returned by subquery.
expr LIKE ANY (subquery) at least one value of the set of values returned by subquery; is false if subquery returns no values.
expr LIKE ALL (subquery) each individual value in the set of values returned by subquery, and is true if subquery returns no values.

The following statement uses the ANY quantifier to retrieve every Example row from the Project table, which contains either the Accounts

Payable or the Accounts Receivable project code:

SELECT * FROM Project

WHERE Proj_Id LIKE ANY

SELECT Proj_Id FROM Charges

WHERE Proj_Id LIKE ANY ('A%')) ;

Teradata RDBMS for UNIX SQL Reference

6-79
SQL Expressions

Logical Expressions: LIKE Partial-String Operator

subquery

If the following form is used, the subquery may return none, one, or several values.

expr

Lnot-I

- LIKE--------quantifier-----( subquery ) -

For example, the following statement:
Previous << 1 .. 79 80 81 82 83 84 < 85 > 86 87 88 89 90 91 .. 241 >> Next