Total Pageviews


How to implement indexOf in Oracle

In Java, we can use indexOf method if we would like to return the index within this string of the first occurrence of the specified character. For instance, if we would like to get the characters which before left parenthesis, we can do this way. 

    public static void main(String[] args) {
        String str1 = "ABC(12345)";
        String str2 = "BC(34567)";
        System.out.println(str1.substring(0, str1.indexOf("(")));
        System.out.println(str2.substring(0, str2.indexOf("(")));

But Oracle does not provide OOTB(Out of The Box) function to fulfill this requirement.

How to do 
We can make good use of INSTR function, the syntax is:

The INSTR functions search string for substring. The function returns an integer indicating the position of the character in string that is the first character of this occurrence. INSTR calculates strings using characters as defined by the input character set. INSTRB uses bytes instead of characters. INSTRC uses Unicode complete characters. INSTR2 uses UCS2 code points. INSTR4 uses UCS4 code points.

  • position is an nonzero integer indicating the character of string where Oracle Database begins the search. If position is negative, then Oracle counts backward from the end of string and then searches backward from the resulting position.
  • occurrence is an integer indicating which occurrence of string Oracle should search for. The value of occurrence must be positive.

Here has an example to fulfill this requirement:
SELECT SUBSTR('ABC(12345)', 0, INSTR('ABC(12345)', '(', 1, 1)-1) FROM DUAL;
SELECT SUBSTR('BC(34567)', 0, INSTR('BC(34567)', '(', 1, 1)-1 ) FROM DUAL;


No comments: