Neoview SQL Reference Manual (R2.4 SP2)

into a CHAR(4) column becomes 'JOE ' (three characters plus one blank). The LIKE predicate
is true only if the column value and the comparison value are the same length. The column value
'JOE ' does not match 'JOE' but does match 'JOE%'.
Comparing the Pattern to VARCHAR Columns
Columns of variable-length character data types do not include trailing blanks unless blanks are
specified when data is entered. For example, the value 'JOE' inserted in a VARCHAR(4) column
is 'JOE' with no trailing blanks. The value matches both 'JOE' and 'JOE%'.
If you cannot locate a value in a variable-length character column, it might be because trailing
blanks were specified when the value was inserted into the table. For example, a value of '5MB
' (with one trailing blank) will not be located by LIKE '%MB' but will be located by LIKE
'%MB%'.
Matching Double-Byte SJIS Characters
In double-byte SJIS characters, the second byte might be the hexadecimal value, 0x5C or 0x5F.
In Neoview SQL, 0x5C corresponds to a back slash (\), which is often used as an escape character,
and 0x5F corresponds to an underscore (_), which is one of the wild-card characters. In Neoview
Release 2.3, if a LIKE pattern includes a double-byte SJIS character that uses 0x5C or 0x5F as the
second byte, the SELECT statement might return the wrong results because Neoview SQL
interprets the second byte as an escape sequence or a wild-card character. In Neoview Release
2.4, character strings are compared at the character level, not the byte level. Therefore, the second
byte of a double-byte SJIS character in a LIKE pattern is treated as part of the SJIS character and
not as an escape sequence or a wild-card character. For more information on using SJIS and other
multibyte character sets, see the Neoview Character Sets Administrator's Guide.
Examples
• Find all employee last names beginning with ZE:
last_name LIKE 'ZE%'
• Find all part descriptions that are not 'FLOPPY_DISK':
partdesc NOT LIKE 'FLOPPY\_DISK' ESCAPE '\'
The escape character indicates that the underscore in 'FLOPPY_DISK' is part of the string
to search for, not a wild-card character.
NULL Predicate
The NULL predicate determines whether all the expressions in a sequence are null. See “Null”
(page 270).
row-value-constructor IS [NOT] NULL
row-value-constructor is:
(expression [,expression]...)
| row-subquery
row-value-constructor
specifies the operand of the NULL predicate. The operand can be either of these:
(expression [,expression ]...)
is a sequence of SQL value expressions, separated by commas and enclosed in parentheses.
expression cannot include an aggregate function unless expression is in a HAVING
clause. expression can be a scalar subquery (a subquery that returns a single row
consisting of a single column). See “Expressions” (page 243).
row-subquery
is a subquery that returns a single row (consisting of a sequence of values). See “Subquery”
(page 294).
Predicates 283