select decode(substr('13542',3,3),'542','true','false') from dual
--返回值:0 true 1 false create or replace function f_compare ( i_num_3 in int, --输入3位数 i_num_5 in int --输入5位数 ) return int is o_res int; str_num_3 varchar2(3); str_num_5 varchar2(5); pos int; begin str_num_3 := to_char(i_num_3); str_num_5 := substr(to_char(i_num_5),3); for pos in 1..3 loop if instr(str_num_3,substr(str_num_5,pos,1)) = 0 then o_res := 1; --false return o_res; end if; end loop; o_res := 0; --true return o_res; exception when others then o_res := 1; --false return o_res; end f_compare;
先将452 与 542排序之后,在做等值判断 WITH t AS (SELECT substr(a.chr1,b.rn,1) chr1, substr(a.chr2,b.rn,1) chr2 FROM (SELECT '452' chr1,'542' chr2 FROM dual) a, (select level rn from dual connect by 1 = 1 and level <= 10) b WHERE length(a.chr1)>=b.rn ) SELECT decode(COUNT(*),0,'fasle','true') aa FROM (SELECT replace(wm_concat(chr1),',','') chr1 FROM (SELECT chr1 FROM t ORDER BY chr1) ) a, (SELECT replace(wm_concat(chr2),',','') chr2 FROM (SELECT chr2 FROM t ORDER BY chr2) ) b WHERE a.chr1 = b.chr2;
--对452排序 SELECT replace(wm_concat(chr1),',','') chr1 FROM (SELECT substr(a.chr1,b.rn,1) chr1 FROM (SELECT '452' chr1,'542' chr2 FROM dual) a, (select level rn from dual connect by 1 = 1 and level <= 10) b WHERE length(a.chr1)>=b.rn ORDER BY substr(a.chr1,b.rn,1) )
create or replace function f_compare ( pi_num_1 in number, -- 3位数 pi_num_2 in number -- 5位数 ) return pls_integer -- 1:相等;0:不相等 as str_l_1 varchar2(10); str_l_2 varchar2(10); i_l_index pls_integer; begin str_l_2 := pi_num_2; for i in 1 .. length(pi_num_1) loop str_l_1 := substr(pi_num_1, i, 1); i_l_index := instr(str_l_2, str_l_1, 1, 1); if i_l_index = 0 then return 0; else str_l_2 := substr(str_l_2, 1, i_l_index - 1) || substr(str_l_2, i_l_index + 1); end if; end loop; return 1; end f_compare;示例: select f_compare('123', '69132') from dual;
CREATE OR REPLACE FUNCTION num_like(i_num1 in number, i_num2 in number) RETURN NUMBER IS v_num1 varchar2(38); v_num2 varchar2(38); BEGIN v_num1 := to_char(i_num1); v_num2 := to_char(i_num2); IF substr(v_num1,-3)=substr(v_num2,-3) THEN RETURN 1; ELSE RETURN 0; END IF; END; /
-- 方法一: CREATE OR REPLACE FUNCTION num_like(i_num1 in number, i_num2 in number) RETURN NUMBER IS v_num1 varchar2(38); v_num2 varchar2(38); BEGIN v_num1 := to_char(i_num1); v_num2 := to_char(i_num2); IF substr(v_num1,-3)=substr(v_num2,-3) THEN RETURN 1; ELSE RETURN 0; END IF; END; /-- 方法二:(也许效率不一定比方法二高)! CREATE OR REPLACE FUNCTION num_like(i_num1 in number, i_num2 in number) RETURN NUMBER IS BEGIN IF mod(v_num1,1000)=mod(v_num2,1000) THEN RETURN 1; ELSE RETURN 0; END IF; END; /-- 最好是用这个函数去查询大量数据的表测试一下两个函数的效率!
-- 方法一(用字符串比较法,定义变量): CREATE OR REPLACE FUNCTION num_like(i_num1 in number, i_num2 in number) RETURN NUMBER IS v_num1 varchar2(38); v_num2 varchar2(38); BEGIN v_num1 := to_char(i_num1); v_num2 := to_char(i_num2); IF substr(v_num1,-3)=substr(v_num2,-3) THEN RETURN 1; ELSE RETURN 0; END IF; END; /-- 方法二(用字符串比较法,不定义变量): CREATE OR REPLACE FUNCTION num_like(i_num1 in number, i_num2 in number) RETURN NUMBER IS BEGIN IF substr(v_num1,-3)=substr(v_num2,-3) THEN RETURN 1; ELSE RETURN 0; END IF; END; /-- 方法三:(用取余数法,也许效率不一定比方法一、二高)! CREATE OR REPLACE FUNCTION num_like(i_num1 in number, i_num2 in number) RETURN NUMBER IS BEGIN IF mod(v_num1,1000)=mod(v_num2,1000) THEN RETURN 1; ELSE RETURN 0; END IF; END; /
-- 如果一定要限制3位数跟5位数比较的话,可以这么写: CREATE OR REPLACE FUNCTION num_like(i_num1 in number, i_num2 in number) RETURN NUMBER IS BEGIN IF substr(i_num1,-3)=substr(i_num2,-3) AND ( length(i_num1)=5 OR length(i_num2)=5 ) THEN RETURN 1; ELSE RETURN 0; END IF; END; /
SQL> select * from v$version;BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 ProductionTNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - ProductionSQL> SQL> --·µ»ØÖµ£º0 true 1 false SQL> create or replace function f_compare 2 ( 3 i_num_3 in int, --ÊäÈë3λÊý 4 i_num_5 in int --ÊäÈë5λÊý 5 ) return int is 6 o_res int; 7 str_num_3 varchar2(3); 8 str_num_5 varchar2(5); 9 pos int; 10 begin 11 str_num_3 := to_char(i_num_3); 12 str_num_5 := substr(to_char(i_num_5),3); 13 for pos in 1..3 loop 14 if instr(str_num_3,substr(str_num_5,pos,1)) = 0 then 15 o_res := 1; --false 16 return o_res; 17 end if; 18 end loop; 19 o_res := 0; --true 20 return o_res; 21 exception 22 when others then 23 o_res := 1; --false 24 return o_res; 25 end f_compare; 26 /Function createdSQL> select f_compare(123,54321) from dual;F_COMPARE(123,54321) -------------------- 0SQL> select f_compare(123,54111) from dual;F_COMPARE(123,54111) -------------------- 0SQL> 123,54111 这个比较应该是False
LZ的问题可以转为: 比较二个字符串的末N位字符是否相等(不限顺序)。
-- 呵呵,理解错啦,这样应该是正确的啦:CREATE OR REPLACE FUNCTION num_like(i_num1 in number, i_num2 in number) RETURN NUMBER IS v_num1 varchar2(38); v_num2 varchar2(38); v_num2_1 varchar2(1); v_num2_2 varchar2(1); v_num2_3 varchar2(1); BEGIN v_num1 := substr(i_num1,-3); v_num2 := substr(i_num2,-3); v_num2_1 := substr(v_num2,1,1); v_num2_2 := substr(v_num2,2,1); v_num2_3 := substr(v_num2,3,1); IF v_num1=v_num2 or v_num1=v_num2_1||v_num2_3||v_num2_2 or v_num1=v_num2_2||v_num2_1||v_num2_3 or v_num1=v_num2_2||v_num2_3||v_num2_1 or v_num1=v_num2_3||v_num2_1||v_num2_2 or v_num1=v_num2_3||v_num2_2||v_num2_1 THEN RETURN 1; ELSE RETURN 0; END IF; END; /
create or replace function f_sort(n int, v_in varchar2) return varchar2 is v_str varchar2(50); begin SELECT replace(wm_concat(chr1),',','') chr1 into v_str FROM (SELECT substr(a.chr1,b.rn,1) chr1 FROM (SELECT substr(v_in, -n) chr1 FROM dual) a, (select level rn from dual connect by 1 = 1 and level <= n) b WHERE length(a.chr1)>=b.rn ORDER BY substr(a.chr1,b.rn,1) ); return v_str; end;create or replace function f_comp(n int, v_in_1 varchar2, v_in_2 varchar2) return int is o_res int; begin if length(v_in_1) < n or length(v_in_2) < n then o_res := 1; --false else if f_sort(n, v_in_1) <> f_sort(n, v_in_2) then o_res := 1; else o_res := 0; end if; end if; return o_res; exception when others then o_res := 1; --false return o_res; end f_comp; SQL> select f_comp(9,'1234567890','17654320981') from dual;F_COMP(9,'1234567890','1765432 ------------------------------ 1SQL> select f_comp(9,'1234567890','1765432098') from dual;F_COMP(9,'1234567890','1765432 ------------------------------ 0
create or replace function Example1(n1 in INT, n2 in INT) return INT IS v1 VARCHAR2(5); --定义变量1存取数字n1 v2 VARCHAR2(3);--定义变量2存取数字n2 v3 VARCHAR2(3);--定义变量3存取数字n1截断后三位的值 pos INT;--遍历v3位置 return_value NUMBER;--返回值,为3表示相等 begin v1 := to_char(n1); v2 := to_char(n2); v3 := substr(v2,3,3); pos := 1; return_value := 0; FOR pos IN 1..3 LOOP IF instr(v1,substr(v3,pos,1)) = 0 --如果v1中没有发现v3中的数字 THEN NULL; --则什么都不做 ELSE return_value := return_value+1; --否则值加1 END IF; END LOOP; RETURN return_value; --如果返回3则表示相等end Example1; 测试代码如下: DECLARE v NUMBER; BEGIN v := example1('12345','345'); dbms_output.put_line(v); END;
--返回值:0 true 1 false
create or replace function f_compare
(
i_num_3 in int, --输入3位数
i_num_5 in int --输入5位数
) return int is
o_res int;
str_num_3 varchar2(3);
str_num_5 varchar2(5);
pos int;
begin
str_num_3 := to_char(i_num_3);
str_num_5 := substr(to_char(i_num_5),3);
for pos in 1..3 loop
if instr(str_num_3,substr(str_num_5,pos,1)) = 0 then
o_res := 1; --false
return o_res;
end if;
end loop;
o_res := 0; --true
return o_res;
exception
when others then
o_res := 1; --false
return o_res;
end f_compare;
WITH t AS
(SELECT substr(a.chr1,b.rn,1) chr1,
substr(a.chr2,b.rn,1) chr2
FROM
(SELECT '452' chr1,'542' chr2 FROM dual) a,
(select level rn from dual connect by 1 = 1 and level <= 10) b
WHERE length(a.chr1)>=b.rn
)
SELECT decode(COUNT(*),0,'fasle','true') aa FROM
(SELECT replace(wm_concat(chr1),',','') chr1 FROM (SELECT chr1 FROM t ORDER BY chr1) ) a,
(SELECT replace(wm_concat(chr2),',','') chr2 FROM (SELECT chr2 FROM t ORDER BY chr2) ) b
WHERE a.chr1 = b.chr2;
--对452排序
SELECT replace(wm_concat(chr1),',','') chr1
FROM
(SELECT substr(a.chr1,b.rn,1) chr1 FROM
(SELECT '452' chr1,'542' chr2 FROM dual) a,
(select level rn from dual connect by 1 = 1 and level <= 10) b
WHERE length(a.chr1)>=b.rn
ORDER BY substr(a.chr1,b.rn,1)
)
(
pi_num_1 in number, -- 3位数
pi_num_2 in number -- 5位数
)
return pls_integer -- 1:相等;0:不相等
as
str_l_1 varchar2(10);
str_l_2 varchar2(10);
i_l_index pls_integer;
begin
str_l_2 := pi_num_2; for i in 1 .. length(pi_num_1) loop
str_l_1 := substr(pi_num_1, i, 1); i_l_index := instr(str_l_2, str_l_1, 1, 1);
if i_l_index = 0 then
return 0;
else
str_l_2 := substr(str_l_2, 1, i_l_index - 1) || substr(str_l_2, i_l_index + 1);
end if;
end loop; return 1;
end f_compare;示例:
select f_compare('123', '69132') from dual;
RETURN NUMBER IS
v_num1 varchar2(38);
v_num2 varchar2(38);
BEGIN
v_num1 := to_char(i_num1);
v_num2 := to_char(i_num2); IF substr(v_num1,-3)=substr(v_num2,-3) THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;
/
CREATE OR REPLACE FUNCTION num_like(i_num1 in number, i_num2 in number)
RETURN NUMBER IS
v_num1 varchar2(38);
v_num2 varchar2(38);
BEGIN
v_num1 := to_char(i_num1);
v_num2 := to_char(i_num2); IF substr(v_num1,-3)=substr(v_num2,-3) THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;
/-- 方法二:(也许效率不一定比方法二高)!
CREATE OR REPLACE FUNCTION num_like(i_num1 in number, i_num2 in number)
RETURN NUMBER IS
BEGIN
IF mod(v_num1,1000)=mod(v_num2,1000) THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;
/-- 最好是用这个函数去查询大量数据的表测试一下两个函数的效率!
-- 方法一(用字符串比较法,定义变量):
CREATE OR REPLACE FUNCTION num_like(i_num1 in number, i_num2 in number)
RETURN NUMBER IS
v_num1 varchar2(38);
v_num2 varchar2(38);
BEGIN
v_num1 := to_char(i_num1);
v_num2 := to_char(i_num2); IF substr(v_num1,-3)=substr(v_num2,-3) THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;
/-- 方法二(用字符串比较法,不定义变量):
CREATE OR REPLACE FUNCTION num_like(i_num1 in number, i_num2 in number)
RETURN NUMBER IS
BEGIN
IF substr(v_num1,-3)=substr(v_num2,-3) THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;
/-- 方法三:(用取余数法,也许效率不一定比方法一、二高)!
CREATE OR REPLACE FUNCTION num_like(i_num1 in number, i_num2 in number)
RETURN NUMBER IS
BEGIN
IF mod(v_num1,1000)=mod(v_num2,1000) THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;
/
CREATE OR REPLACE FUNCTION num_like(i_num1 in number, i_num2 in number)
RETURN NUMBER IS
BEGIN
IF substr(i_num1,-3)=substr(i_num2,-3) AND ( length(i_num1)=5 OR length(i_num2)=5 )
THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;
/
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 ProductionTNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - ProductionSQL>
SQL> --·µ»ØÖµ£º0 true 1 false
SQL> create or replace function f_compare
2 (
3 i_num_3 in int, --ÊäÈë3λÊý
4 i_num_5 in int --ÊäÈë5λÊý
5 ) return int is
6 o_res int;
7 str_num_3 varchar2(3);
8 str_num_5 varchar2(5);
9 pos int;
10 begin
11 str_num_3 := to_char(i_num_3);
12 str_num_5 := substr(to_char(i_num_5),3);
13 for pos in 1..3 loop
14 if instr(str_num_3,substr(str_num_5,pos,1)) = 0 then
15 o_res := 1; --false
16 return o_res;
17 end if;
18 end loop;
19 o_res := 0; --true
20 return o_res;
21 exception
22 when others then
23 o_res := 1; --false
24 return o_res;
25 end f_compare;
26 /Function createdSQL> select f_compare(123,54321) from dual;F_COMPARE(123,54321)
--------------------
0SQL> select f_compare(123,54111) from dual;F_COMPARE(123,54111)
--------------------
0SQL>
123,54111 这个比较应该是False
比较二个字符串的末N位字符是否相等(不限顺序)。
RETURN NUMBER IS
v_num1 varchar2(38);
v_num2 varchar2(38);
v_num2_1 varchar2(1);
v_num2_2 varchar2(1);
v_num2_3 varchar2(1);
BEGIN
v_num1 := substr(i_num1,-3);
v_num2 := substr(i_num2,-3);
v_num2_1 := substr(v_num2,1,1);
v_num2_2 := substr(v_num2,2,1);
v_num2_3 := substr(v_num2,3,1); IF v_num1=v_num2 or v_num1=v_num2_1||v_num2_3||v_num2_2 or v_num1=v_num2_2||v_num2_1||v_num2_3 or v_num1=v_num2_2||v_num2_3||v_num2_1
or v_num1=v_num2_3||v_num2_1||v_num2_2 or v_num1=v_num2_3||v_num2_2||v_num2_1
THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;
/
create or replace function f_sort(n int, v_in varchar2) return varchar2
is
v_str varchar2(50);
begin
SELECT replace(wm_concat(chr1),',','') chr1 into v_str FROM
(SELECT substr(a.chr1,b.rn,1) chr1 FROM
(SELECT substr(v_in, -n) chr1 FROM dual) a,
(select level rn from dual connect by 1 = 1 and level <= n) b
WHERE length(a.chr1)>=b.rn
ORDER BY substr(a.chr1,b.rn,1)
);
return v_str;
end;create or replace function f_comp(n int, v_in_1 varchar2, v_in_2 varchar2)
return int is
o_res int;
begin
if length(v_in_1) < n or length(v_in_2) < n then
o_res := 1; --false
else
if f_sort(n, v_in_1) <> f_sort(n, v_in_2) then
o_res := 1;
else
o_res := 0;
end if;
end if;
return o_res;
exception
when others then
o_res := 1; --false
return o_res;
end f_comp;
SQL> select f_comp(9,'1234567890','17654320981') from dual;F_COMP(9,'1234567890','1765432
------------------------------
1SQL> select f_comp(9,'1234567890','1765432098') from dual;F_COMP(9,'1234567890','1765432
------------------------------
0
create or replace function Example1(n1 in INT, n2 in INT) return INT
IS
v1 VARCHAR2(5); --定义变量1存取数字n1
v2 VARCHAR2(3);--定义变量2存取数字n2
v3 VARCHAR2(3);--定义变量3存取数字n1截断后三位的值
pos INT;--遍历v3位置
return_value NUMBER;--返回值,为3表示相等
begin
v1 := to_char(n1);
v2 := to_char(n2);
v3 := substr(v2,3,3);
pos := 1;
return_value := 0;
FOR pos IN 1..3 LOOP
IF instr(v1,substr(v3,pos,1)) = 0 --如果v1中没有发现v3中的数字
THEN NULL; --则什么都不做
ELSE
return_value := return_value+1; --否则值加1
END IF;
END LOOP;
RETURN return_value; --如果返回3则表示相等end Example1;
测试代码如下: DECLARE
v NUMBER;
BEGIN
v := example1('12345','345');
dbms_output.put_line(v);
END;