SQL INSTR Function
SQL INSTR function is used to search a character or string of characters in a specific set of characters and report the position of those characters. You can specify a starting positionfor the search, and you can request that a specific occurrence be found. If position is negative, the search begins from the end of the string. Please note that the search character is case sensitive, means if you are searching for ‘A’ it will return place of capital ‘A’ only but not for small ‘a’.
The syntax is as follows:
INSTR (COLUMN NAME, 'SET', [START POSITION [, OCCURRENCE] ]);
Example:
The following SQL INSTR statement returns the position of the first occurrence of the letter ‘A’ for each name in employee table:
SELECT first_name, INSTR (first_name, ‘C’) FROM employee;
Output:
Example 2:
The following SQL INSTR statement returns the position of 1st ‘E’ with search starting from 2nd letter of each name in employee table:
SELECT INSTR (name, 'E', 2, 1) FROM employee;
Output:
Note: The position is always calculated from first letter regardless of where we start searching in string.