create or replace function get_6char(phone_number in varchar2, result out varchar2) return varchar2 as j int:=1; tmp_char varchar2(13); begin for j in 1..length(phone_number) loop
if substr(phone_number,j,1)=substr(phone_number,j+1,1) then if length(tmp_char)+1=6 then result:=phone_number ; exit; else tmp_char:=tmp_char||substr(phone_number,j+1,1); result:='Not matched'; end if; else tmp_char:=substr(phone_number,j+1,1); end if; end loop; dbms_output.put_line(tmp_char); return result; end;
AABBCC,ABCDE这样的怎么写?楼上的能具体写下吗,谢谢了!
select mc from test t where REGEXP_LIKE(mc,'(.)\1{5}')
function get_6char(phone_number in varchar2, result out varchar2)
return varchar2 as
j int:=1;
tmp_char varchar2(13);
begin
for j in 1..length(phone_number) loop
if substr(phone_number,j,1)=substr(phone_number,j+1,1) then
if length(tmp_char)+1=6 then
result:=phone_number ;
exit;
else
tmp_char:=tmp_char||substr(phone_number,j+1,1);
result:='Not matched';
end if;
else
tmp_char:=substr(phone_number,j+1,1);
end if;
end loop;
dbms_output.put_line(tmp_char);
return result;
end;