SQL> select binary_search('ABCDEFG', 'F') from dual;
BINARY_SEARCH('ABCDEFG','F')
----------------------------
6
SQL> select instr('ABCDEFG', 'F') from dual;
INSTR('ABCDEFG','F')
--------------------
6
<pre name="code" class="sql">create or replace function binary_search(string1 in varchar2,
string2 in varchar2) return number is
--二分查找不重复字符串
--create by renweixin 2015/3/18
len int;
i int := 1;
middle int;
target varchar2(60);
position int;
flag varchar2(1) := 'N';
begin
len := length(string1);
middle := len / 2;
position := middle;
while (flag = 'N') loop
target := substr(string1, position, 1);
if target < string2 then
position := position + middle / (2 * i);
elsif target > string2 then
position := middle / (2 * i);
else
flag := 'Y';
end if;
i := i + 1;
end loop;
return position;
end binary_search;