instr

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

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