Neoview SQL Reference Manual (R2.4)

UPSHIFT Function
The UPSHIFT function upshifts alphanumeric characters. For non-alphanumeric characters,
UCASE returns the same character. UPSHIFT can appear anywhere in a query where a value
can be used, such as in a select list, an ON clause, a WHERE clause, a HAVING clause, a LIKE
predicate, an expression, or as qualifying a new value in an UPDATE or INSERT statement. The
result returned by the UPSHIFT function is equal to the result returned by the “UPPER Function”
(page 472) or “UCASE Function” (page 464).
UPSHIFT returns a string of fixed-length or variable-length character data, depending on the
data type of the input string.
UPSHIFT is a Neoview SQL extension.
UPSHIFT (character-expression)
character-expression
is an SQL character value expression that specifies a string of characters to upshift. See
“Character Value Expressions” (page 240).
Considerations for UPSHIFT
For one-to-one mappings for the UCS2 character set, see “Considerations for UCASE” (page 464).
Examples of UPSHIFT
• Suppose that your CUSTOMER table includes an entry for Hotel Oregon. Select the column
CUSTNAME and return a result in uppercase and lowercase letters by using the UPSHIFT,
UPPER, and LOWER functions:
SELECT UPSHIFT(custname),UPPER(custname),UCASE(custname)
FROM sales.customer;
(EXPR) (EXPR) (EXPR)
----------------- ------------------- ------------------
... ... ...
HOTEL OREGON HOTEL OREGON HOTEL OREGON
--- 17 row(s) selected.
• Perform a case-insensitive search for the DataSpeed customer:
SELECT *
FROM sales.customer
WHERE UPSHIFT (custname) = 'DATASPEED';
CUSTNUM CUSTNAME STREET CITY ...
------- ---------- -------------------- --------- ...
1234 DataSpeed 300 SAN GABRIEL WAY NEW YORK ...
--- 1 row(s) selected.
In the table, the name can be in lowercase, uppercase, or mixed case letters.
• Suppose that your database includes two department tables: DEPT1 and DEPT2. Return all
rows from the two tables in which the department names have the same value regardless
of case:
SELECT * FROM persnl.dept1 D1, persnl.dept2 D2
WHERE UPSHIFT(D1.deptname) = UPSHIFT(D2.deptname);
UPSHIFT Function 473