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

expr

I----------1 LIKE -,-------------1- (subquery)

INOTI L quantifier-I L

-NOT

- (Jl-exp^L) ----------- LIKE ---------.-( subquery) -|

I NOT I I quantifier I I

ESCAPE escape_character

J

where:

Lnot-I

quantifier ESCAPE escape_character

-L Dattern string -L'

T

- LIKE quantifier (-Lpattern_string J-) -

U

r

ESCAPE escape_character

HH01A043

Syntax Element . . . Is . . .
expr A character String or character String expreSSion argument.
pattern_string A character String literal argument.
pattern_expr A character String or a character expreSSion argument.
quantifier The quantifier argument is one of the keywords ANY (or SOME) or ALL. SOME iS a Synonym for ANY.
subquery A SELECT statement argument.
ESCAPE escape_character Keyword Specifying a Single eScape character (single or multibyte). It can also be in xxxxxg format.

expr

If any expression in a comparison is null, the result of the comparison is unknown. For a LIKE operation to provide a true reSult when Searching fieldS that may contain null valueS, the statement must include the IS [NOT] NULL operator.

6-74

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

Logical Expressions: LIKE Partial-String Operator

pattern-string

ESCAPE Feature of LIKE

If neither the string being matched nor pattern-string has been designated CASESPECIFIC, any lowercase letters are converted to uppercase before the comparison operation occurs.

If either the string being matched or the pattern-string has been designated CASESPECIFIC, letters are considered matched only if they are the same letters and the same case.

The pattern_string must always be placed to the right of the LIKE operator.

The pattern_string may be any character string.

The % and _ characters may be used in any combination in pattern-string.

The % character represents any string of zero or more arbitrary characters.

The _ (underscore) represents exactly one arbitrary character.

Any single character is acceptable in the position in which the underscore character appears, and any string of characters is acceptable as a replacement for the percent. However, the underscore and percent characters cannot be used in a pattern.

To get around this, specify a single escape character in addition to the pattern_string. See the previous page, for the LIKE predicate syntax.

When the defined escape character is used in the pattern string it must be immediately followed by either an underscore, percent sign, or another escape character.

In a left-to-right scan of the pattern string the following rules apply when ESCAPE is specified:

Until an instance of the escape character occurs, characters in the pattern are interpreted at face value.

When an escape character occurs immediately followed by another instance of the escape character, the two character sequence is treated as though it were a single instance of the escape character, considered as a normal character.

When an escape character occurs immediately followed by an underscore, the sequence is treated as a single underscore character (not a wildcard character).

When an escape character occurs immediately followed by a percent character, the sequence is treated as a single percent character (not a wildcard character).

Teradata RDBMS for UNIX SQL Reference

6-75
SQL Expressions

Logical Expressions: LIKE Partial-String Operator

When an escape character is not immediately followed by an underscore, a percent, or another instance of itself, the scan stops and an error is reported.

The following example illustrates the use of ESCAPE:

Example6

To look for the pattern 95% in a string such as Result is 95% effective, if Result is the field to be checked, use:

WHERE Result LIKE '%95Z%%' ESCAPE 'Z'

This clause finds the value 95%.

The following notes apply to blank characters and how they are Blanks treated in strings:

Blanks are significant in both the character expression, and in the pattern string.

When using pattern matching, be aware that both leading and trailing blanks in the field or expression must match exactly with the pattern.

For example, A%BC matches AxxBC, but not AxxBCb, and A%BCb matches AxxBCb, but not AxxBC or AxxBCbb (b indicates a blank).

To retrieve the row in all cases, consider using the TRIM function, which removes both leading and trailing blanks from the source string before doing the pattern match.

For example, to remove trailing blanks:

TRIM (TRAILING FROM expr) LIKE pattern-string

To remove leading and trailing blanks:

TRIM (BOTH FROM expr) LIKE pattern-string

If the pattern_string is forced to a fixed length (perhaps by a macro parameter or a USING clause), trailing blanks may be appended. In such cases, the field must contain the same number of trailing blanks in order to match.

For example:

CREATE MACRO (pattern (CHAR(5)) AS field LIKE :pattern...
Previous << 1 .. 78 79 80 81 82 83 < 84 > 85 86 87 88 89 90 .. 241 >> Next