The LOCATE string function has an optional third parameter, which may be supplied to provide a starting offset for the LOCATE string search.The syntax for LOCATE is:
LOCATE(string-expression1, string-expression2 [, numeric-expression])
If string-expression1, the string to be searched, is a CLOB data type and the numeric-expression provided for the offset is evaluated as a BIGINT data type, the offset may be interpreted incorrectly.
Workaround: To ensure the offset is evaluated correctly, either use an integer constant or CAST the numeric-expression as an integer.
This example illustrates both workarounds.
CREATE TABLE t1(c_clob CLOB, c_bigint BIGINT); INSERT INTO t1 VALUES ('abc abc def', 4);
CAST numeric-expression as an integer:
SELECT LOCATE( c_clob, 'abc', CAST(LOCATE(c_clob,'abc def') as INT)) FROM t1; SELECT LOCATE( c_clob, 'abc', CAST(c_bigint as INT)) FROM t1;
Or use an integer constant:
SELECT LOCATE( c_clob, 'abc', 4) FROM t1;
For more information on the LOCATE function, refer to Reference: Building Blocks, Tables, and Procedures.