SQL/MX 3.1 Reference Manual (H06.23+, J06.12+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Release 3.1 Reference Manual—663850-001
9-86
LOCATE Function
•
In this example, LNNVL (colB > colC), SELECT statement selects the records from
the column where colB is not greater than colC including NULL values. The first
record has a NULL value for colC, hence LNNVL function returns TRUE for that
record.
Select * from tab1 where LNNVL(colB>colC);
COLA COLB COLC
---------- ----------- -----------
100 20 ?
300 8 12
400 0 11
--- 3 row(s) selected.
•
The LNNVL (colC is NULL), SELECT statement selects the records where colC is
NOT NULL.
Select * from tab1 where LNNVL(colC is NULL);
COLA COLB COLC
----------- ----------- -----------
200 15 8
300 8 12
400 10 11
300 10 8
300 10 6
--- 5 row(s) selected.
LOCATE Function
The LOCATE function searches for a given substring in a character string. If the
substring is found, NonStop SQL/MX returns the character position of the substring
within the string. The result returned by the LOCATE function is equal to the result
returned by the POSITION function.
LOCATE is an SQL/MX extension.
substring-expression
is an SQL character value expression that specifies the substring to search for in
source-expression. The substring-expression cannot be NULL. See
Character Value Expressions on page 6-41.
LOCATE (substring-expression,source-expression)