SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Functions and Expressions

HP NonStop SQL/MX Reference Manual—540440-003

9-106

Examples of POSITION

•

This function returns the value 8 for the position of the substring ‘John’ within the

string:

POSITION ('John' IN 'Robert John Smith')

•

Suppose that the EMPLOYEE table has an EMPNAME column that contains both

the first and last names. Return all records in table EMPLOYEE that contain the

substring 'Smith' regardless of whether the column value is in uppercase or

lowercase characters:

SELECT * FROM persnl.employee

WHERE POSITION ('SMITH' IN UPSHIFT(empname)) > 0 ;

POWER Function

The POWER function returns the value of a numeric value expression raised to the

power of an integer value expression. You can also use the exponential operator **.

POWER is an SQL/MX extension.

numeric-expression-1,numeric-expression-2

are SQL numeric value expressions that specify the values for the base and

exponent arguments of the POWER function. See Numeric Value Expressions on

page 6-48.

If base numeric-expression-1 is zero, the exponent numeric-

expression-2 must be greater than zero, and the result is zero. If the exponent

is zero, the base cannot be 0, and the result is 1. If the base is negative, the

exponent must be a value with an exact numeric data type and a scale of zero.

Examples of POWER

•

Return the value 15.625:

POWER (2.5,3)

•

Return the value 27. The function POWER raised to the power of 2 is the inverse

of the function SQRT:

POWER (SQRT(27),2)

POWER (numeric-expression-1,numeric-expression-2)