User Tools

Site Tools

A PCRE internal error occured. This might be caused by a faulty plugin

Sidebar

**HPL/SQL is included to Apache Hive since version 2.0** * [[home|Home]]\\ * [[why|Why HPL/SQL]]\\ * [[features|Key Features]]\\ * [[start|Get Started]]\\ * [[doc|HPL/SQL Reference]]\\ * [[download|Download]]\\ * [[new|What's New]]\\ * [[about|About]]

instr

====== 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