Neoview SQL Reference Manual (R2.4)

Using NOT
If you specify NOT, the predicate is true if the pattern does not match any string in the
match-value or is not the same length as any string in the match-value. For example, NAME
NOT LIKE '_Z' is true if the string is not two characters long or the last character is not Z. In
a search condition, the predicate NAME NOT LIKE '_Z' is equivalent to NOT (NAME LIKE
'_Z').
Wild-Card Characters
You can look for similar values by specifying only part of the characters of pattern combined
with these wild-card characters:
• “Percent Sign (%)” (page 276)
• “Underscore (_)” (page 276)
Percent Sign (%)
Use a percent sign to indicate zero or more characters of any type. For example, '%ART%' matches
'SMART', 'ARTIFICIAL', and 'PARTICULAR', but not 'smart'.
Underscore (_)
Use an underscore to indicate any single character. For example, 'BOO_' matches 'BOOK' and
'BOOT' but not 'BOO', 'BOOKLET', or 'book'.
In Neoview Release 2.3, the underscore (_) matches only one byte. To use the underscore
effectively with multibyte characters, you must know the byte length of the multibyte character.
Otherwise, you might not get the results that you want using the LIKE predicate. If the Neoview
platform uses the SJIS configuration and you want to match one SJIS character, you must specify
one or two underscore characters in the LIKE pattern, depending on the number of bytes that
make up a single SJIS character. If the Neoview platform uses the Unicode configuration and
you want to match one UTF8 character, you must specify one to four underscore characters in
the LIKE pattern, depending on the number of bytes that make up a single UTF8 character.
In Neoview Release 2.4, one underscore always matches one character, regardless of the byte
length of the character. When matching a single multibyte character in the database, specify one
underscore in the LIKE pattern. For more information on using multibyte character sets, see the
Neoview Character Sets Administrator's Guide.
Escape Characters
To search for a string containing a percent sign (%) or an underscore (_), define an escape character,
using ESCAPE esc-char-expression, to turn off the special meaning of the percent sign and
underscore.
To include a percent sign or an underscore in a comparison string, type the escape character
immediately preceding the percent sign or underscore. For example, to locate the value 'A_B',
type:
NAME LIKE 'A\_B' ESCAPE '\'
To include the escape character itself in the comparison string, type two escape characters. For
example, to locate 'A_B\C%', type:
NAME LIKE 'A\_B\\C\%' ESCAPE '\'
The escape character must precede only the percent sign, underscore, or escape character itself.
For example, the pattern RA\BS is an invalid LIKE pattern if the escape character is defined to
be '\'. Error 8410 will be returned if this kind of pattern is used in a SQL query.
Comparing the Pattern to CHAR Columns
Columns of data type CHAR are fixed length. When a value is inserted into a CHAR column,
Neoview SQL pads the value in the column with blanks if necessary. The value 'JOE' inserted
276 SQL Language Elements