create function is_a_z(p_str in varchar2)
return number
as
num number;
begin
for i in 65..91 loop
num:=instr(p_str,chr(i));
if num>0 then
return 1;
else
return 0;
end if;
end loop;
end;
/select test_str from test_table where is_a_z(test_str)=1;
return number
as
num number;
begin
for i in 65..91 loop
num:=instr(p_str,chr(i));
if num>0 then
return 1;
else
return 0;
end if;
end loop;
end;
/select test_str from test_table where is_a_z(test_str)=1;
return number
as
num number;
begin
for i in 65..91 loop
num:=instr(p_str,chr(i));
if num>0 then
return 1;
end if;
/
select * from test_table where is_a_z(test_str)=1;
end loop;
return 0;
end;
/
return number
as
num number;
begin
for i in 65..90 loop
num:=instr(p_str,chr(i));
if num>0 then
return 1;
end if;
/
select * from test_table where is_a_z(test_str)=1;
end loop;
return 0;
end;
/
return number
as
num number;
begin
for i in 65..90 loop
num:=instr(p_str,chr(i));
if num>0 then
return 1;
end if;
end loop;
return 0;
end;
/
select * from test_table where is_a_z(test_str)=1;
return number
is
w_char char(1);
w_pos number;
begin
for i in length(p_str) loop
w_char := substr(p_str,i,1);
if w_char >='A' and w_char <='Z' then
return 1;
end if;
end loop;
return 0;
end if;
/
select * from test_table where test_str like '%A%' or test_str like '%B%' or test_str like '%C%' or test_str like '%D%' or test_str like '%E%' or test_str like '%F%' or test_str like '%G%' or test_str like '%H%' or test_str like '%I%' or test_str like '%J%' or test_str like '%K%' or test_str like '%L%' or test_str like '%M%' or test_str like '%N%' or test_str like '%O%' or test_str like test_str '%P%' or test_str like '%Q%' or test_str like '%R%' or test_str like '%S%' or test_str like '%T%' or test_str like '%U%' or test_str like '%V%' or test_str like '%W%' or test_str like '%X%' or test_str like '%Y%' or test_str like '%Z%'
1 oracle like 子句没有内置对诸如%[A-Z]%,%[0-9]%模式匹配得支持,偶觉得这是很失败的;
2 如果我们写成function的话,大规模的查询恐怕效率不太高,好像分析计划中,产生对定义函数的匿名调用--假设目标表有100万rows的话,那么就是100万次调用!
3 而且偶提的模型是抽象出来的,实际情况也许会更复杂,555555555~~~~~
create function is_a_z(p_str in varchar2)
return number
as
num number;
begin
for i in 65..90 loop
num:=instr(p_str,chr(i));
if num>0 then
return 1;
end if;
end loop;
return 0;
end;
/
对于楼主的话话,太严重了,每个产品都有其不足,只能让它以后完善