This shows you the differences between two versions of the page.
— |
instr [2015/09/23 20:27] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ====== INSTR Function - PL/HQL ====== | ||
+ | INSTR function returns the starting position of a substring within a string. | ||
+ | |||
+ | **Syntax**: | ||
+ | |||
+ | <code language="sql"> | ||
+ | INSTR(string, substring [, position [, occurrence]]) | ||
+ | </code> | ||
+ | |||
+ | **Notes**: | ||
+ | |||
+ | * //position// specifies the staring position for search, the default is 1 (the beginning of //string//) | ||
+ | * If //position// is negative INSTR counts and searches backward from the end of string | ||
+ | * //occurrence// specifies which occurrence of //substring// to search, the default is 1 (finds the first occurrence) | ||
+ | * If //string// is NULL the return value is NULL | ||
+ | * if //string// is not NULL and //substring// not found the return value is 0 | ||
+ | |||
+ | **Return Type:** | ||
+ | |||
+ | INT | ||
+ | |||
+ | **Example 1**: | ||
+ | |||
+ | Find the first occurrence starting from the beginning: | ||
+ | |||
+ | <code language="sql"> | ||
+ | INSTR('abc', 'b') -- Result 2 | ||
+ | </code> | ||
+ | |||
+ | **Example 2**: | ||
+ | |||
+ | Find the first occurrence starting from the 3rd position: | ||
+ | |||
+ | <code language="sql"> | ||
+ | INSTR('abcabc', 'b', 3) -- Result 5 | ||
+ | </code> | ||
+ | |||
+ | **Example 3**: | ||
+ | |||
+ | Find the second occurrence starting from the 3rd position: | ||
+ | |||
+ | <code language="sql"> | ||
+ | INSTR('abcabcabc', 'b', 3, 2) -- Result 8 | ||
+ | </code> | ||
+ | |||
+ | **Example 4**: | ||
+ | |||
+ | Find the first occurrence starting from the 3rd position from the end: | ||
+ | |||
+ | <code language="sql"> | ||
+ | INSTR('abcabcabc', 'b', -3) -- Result 5 | ||
+ | </code> | ||
+ | |||
+ | **Example 5**: | ||
+ | |||
+ | Find the second occurrence starting from the 3rd position from the end: | ||
+ | |||
+ | <code language="sql"> | ||
+ | INSTR('abcabcabc', 'b', -3, 2) -- Result 2 | ||
+ | </code> | ||
+ | |||
+ | **Compatibility**: Oracle, IBM DB2 and Teradata. | ||
+ | |||
+ | **Version:** PL/HQL 0.3.11 |