如何写一个sql函数，取两数据串的交集？

Question

写一个sql函数：
testFunction(arg1,arg2);当调用 testFunction('31,32,33','22,32,33,42')时，返回两个串的交集：32,33请问应该如何实现？请高手指点，谢谢！

Answer 1

select rn from(
select regexp_substr('31,32,33','[^,]+',1,rownum) rn
from dual connect by rownum<= length(regexp_replace('31,32,33','[^,]+'))+1
union all
select regexp_substr('22,32,33,42','[^,]+',1,rownum) rn
from dual connect by rownum<= length(regexp_replace('22,32,33,42','[^,]+'))+1
) 
group by rn 
having count(*)>1
————————————————————————————————或者
select regexp_substr('31,32,33','[^,]+',1,rownum) rn
from dual connect by rownum<= length(regexp_replace('31,32,33','[^,]+'))+1
intersect 
select regexp_substr('22,32,33,42','[^,]+',1,rownum) rn
from dual connect by rownum<= length(regexp_replace('22,32,33,42','[^,]+'))+1

Answer 2

create or replace function testFunction
(
Str1 in varchar2,
Str2 in varchar2
) return varchar2 is
result varchar2(4000);
begin
select wm_concat(rn)
  into result
  from (select regexp_substr(Str1, '[^,]+', 1, rownum) rn
  from dual
connect by rownum <= length(regexp_replace(Str1, '[^,]+')) + 1
intersect
select regexp_substr(Str2, '[^,]+', 1, rownum) rn
  from dual
connect by rownum <= length(regexp_replace(Str2, '[^,]+')) + 1);
return result;
end testFunction;

Answer 3

直接调用函数是正常的
select testFunction('31,32,33','31,32,34,35') from dual;
这样是正确的，返回值：31,32 
 
把表的字段作为参数放进去，就不行了。 
select testFunction(property_4,'33'),id from org_user_property_data;
这样查出来的数据不正确。请问是什么原因呢？

Answer 4

表结构
-- Create table
create table ORG_USER_PROPERTY_DATA
(
  ID            NUMBER(18),
  USER_ID       NUMBER(18),
  PROPERTY_0    VARCHAR2(255),
  PROPERTY_1    VARCHAR2(255),
  PROPERTY_2    VARCHAR2(255),
  PROPERTY_3    VARCHAR2(255),
  PROPERTY_4    VARCHAR2(255),
  PROPERTY_5    VARCHAR2(255),
  PROPERTY_6    VARCHAR2(255),
  PROPERTY_7    VARCHAR2(255),
  PROPERTY_8    VARCHAR2(255),
  PROPERTY_9    VARCHAR2(255),
  PROPERTY_10   VARCHAR2(255),
  PROPERTY_11   VARCHAR2(255),
  PROPERTY_12   VARCHAR2(255),
  PROPERTY_13   VARCHAR2(255),
  PROPERTY_14   VARCHAR2(255),
  PROPERTY_15   VARCHAR2(255),
  PROPERTY_16   VARCHAR2(255),
  PROPERTY_17   VARCHAR2(255),
  PROPERTY_18   VARCHAR2(255),
  PROPERTY_19   VARCHAR2(255),
  PROPERTY_20   VARCHAR2(255),
  PROPERTY_21   VARCHAR2(255),
  PROPERTY_22   VARCHAR2(255),
  PROPERTY_23   VARCHAR2(255),
  PROPERTY_24   VARCHAR2(255),
  PROPERTY_25   VARCHAR2(255),
  PROPERTY_26   VARCHAR2(255),
  PROPERTY_27   VARCHAR2(255),
  PROPERTY_28   VARCHAR2(255),
  PROPERTY_29   VARCHAR2(255),
  PROPERTY_30   VARCHAR2(255),
  PROPERTY_31   VARCHAR2(255),
  PROPERTY_32   VARCHAR2(255),
  PROPERTY_33   VARCHAR2(255),
  PROPERTY_34   VARCHAR2(255),
  PROPERTY_35   VARCHAR2(255),
  PROPERTY_36   VARCHAR2(255),
  PROPERTY_37   VARCHAR2(255),
  PROPERTY_38   VARCHAR2(255),
  PROPERTY_39   VARCHAR2(255),
  PROPERTY_40   VARCHAR2(255),
  PROPERTY_41   VARCHAR2(255),
  PROPERTY_42   VARCHAR2(255),
  PROPERTY_43   VARCHAR2(255),
  PROPERTY_44   VARCHAR2(255),
  PROPERTY_45   VARCHAR2(255),
  PROPERTY_46   VARCHAR2(255),
  PROPERTY_47   VARCHAR2(255),
  PROPERTY_48   VARCHAR2(255),
  PROPERTY_49   VARCHAR2(255),
  PROPERTY_50   VARCHAR2(255),
  PROPERTY_51   VARCHAR2(255),
  PROPERTY_52   VARCHAR2(255),
  PROPERTY_53   VARCHAR2(255),
  PROPERTY_54   VARCHAR2(255),
  PROPERTY_55   VARCHAR2(255),
  PROPERTY_56   VARCHAR2(255),
  PROPERTY_57   VARCHAR2(255),
  PROPERTY_58   VARCHAR2(255),
  PROPERTY_59   VARCHAR2(255),
  PROPERTY_60   VARCHAR2(255),
  PROPERTY_61   VARCHAR2(255),
  PROPERTY_62   VARCHAR2(255),
  PROPERTY_63   VARCHAR2(255),
  PROPERTY_64   VARCHAR2(255),
  PROPERTY_65   VARCHAR2(255),
  PROPERTY_66   VARCHAR2(255),
  PROPERTY_67   VARCHAR2(255),
  PROPERTY_68   VARCHAR2(255),
  PROPERTY_69   VARCHAR2(255),
  PROPERTY_70   VARCHAR2(255),
  PROPERTY_71   VARCHAR2(255),
  PROPERTY_72   VARCHAR2(255),
  PROPERTY_73   VARCHAR2(255),
  PROPERTY_74   VARCHAR2(255),
  PROPERTY_75   VARCHAR2(255),
  PROPERTY_76   VARCHAR2(255),
  PROPERTY_77   VARCHAR2(255),
  PROPERTY_78   VARCHAR2(255),
  PROPERTY_79   VARCHAR2(255),
  PROPERTY_80   VARCHAR2(255),
  PROPERTY_81   VARCHAR2(255),
  PROPERTY_82   VARCHAR2(255),
  PROPERTY_83   VARCHAR2(255),
  PROPERTY_84   VARCHAR2(255),
  PROPERTY_85   VARCHAR2(255),
  PROPERTY_86   VARCHAR2(255),
  PROPERTY_87   VARCHAR2(255),
  PROPERTY_88   VARCHAR2(255),
  PROPERTY_89   VARCHAR2(255),
  PROPERTY_90   VARCHAR2(255),
  PROPERTY_91   VARCHAR2(255),
  PROPERTY_92   VARCHAR2(255),
  PROPERTY_93   VARCHAR2(255),
  PROPERTY_94   VARCHAR2(255),
  PROPERTY_95   VARCHAR2(255),
  PROPERTY_96   VARCHAR2(255),
  PROPERTY_97   VARCHAR2(255),
  PROPERTY_98   VARCHAR2(255),
  PROPERTY_99   VARCHAR2(255),
  PROPERTY_100  VARCHAR2(255),
  RECORD_STATUS CHAR(1) default '',
  UPDATE_COUNT  NUMBER(18) default 0,
  CREATOR_ID    NUMBER(18),
  CREATE_DATE   DATE,
  UPDATER_ID    NUMBER(18),
  UPDATE_DATE   DATE
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns 
comment on column ORG_USER_PROPERTY_DATA.USER_ID
  is '用户ID';
-- Create/Recreate indexes 
create unique index ORG_USER_PROPERTY_DATA_PK on ORG_USER_PROPERTY_DATA (ID)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

Answer 5

表数据
insert into org_user_property_data (ID, USER_ID, PROPERTY_0, PROPERTY_1, PROPERTY_2, PROPERTY_3, PROPERTY_4, PROPERTY_5, PROPERTY_6, PROPERTY_7, PROPERTY_8, PROPERTY_9, PROPERTY_10, PROPERTY_11, PROPERTY_12, PROPERTY_13, PROPERTY_14, PROPERTY_15, PROPERTY_16, PROPERTY_17, PROPERTY_18, PROPERTY_19, PROPERTY_20, PROPERTY_21, PROPERTY_22, PROPERTY_23, PROPERTY_24, PROPERTY_25, PROPERTY_26, PROPERTY_27, PROPERTY_28, PROPERTY_29, PROPERTY_30, PROPERTY_31, PROPERTY_32, PROPERTY_33, PROPERTY_34, PROPERTY_35, PROPERTY_36, PROPERTY_37, PROPERTY_38, PROPERTY_39, PROPERTY_40, PROPERTY_41, PROPERTY_42, PROPERTY_43, PROPERTY_44, PROPERTY_45, PROPERTY_46, PROPERTY_47, PROPERTY_48, PROPERTY_49, PROPERTY_50, PROPERTY_51, PROPERTY_52, PROPERTY_53, PROPERTY_54, PROPERTY_55, PROPERTY_56, PROPERTY_57, PROPERTY_58, PROPERTY_59, PROPERTY_60, PROPERTY_61, PROPERTY_62, PROPERTY_63, PROPERTY_64, PROPERTY_65, PROPERTY_66, PROPERTY_67, PROPERTY_68, PROPERTY_69, PROPERTY_70, PROPERTY_71, PROPERTY_72, PROPERTY_73, PROPERTY_74, PROPERTY_75, PROPERTY_76, PROPERTY_77, PROPERTY_78, PROPERTY_79, PROPERTY_80, PROPERTY_81, PROPERTY_82, PROPERTY_83, PROPERTY_84, PROPERTY_85, PROPERTY_86, PROPERTY_87, PROPERTY_88, PROPERTY_89, PROPERTY_90, PROPERTY_91, PROPERTY_92, PROPERTY_93, PROPERTY_94, PROPERTY_95, PROPERTY_96, PROPERTY_97, PROPERTY_98, PROPERTY_99, PROPERTY_100, RECORD_STATUS, UPDATE_COUNT, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE)
values (31, 22, '2007-03-01', '20', '39', '30', '32, 33, 34', '300000', 'kingnode', '新洲村', '', '33', '4', '', '20', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'A', 0, 23, to_date('09-03-2012 15:18:40', 'dd-mm-yyyy hh24:mi:ss'), 23, to_date('20-03-2012 19:08:07', 'dd-mm-yyyy hh24:mi:ss'));
insert into org_user_property_data (ID, USER_ID, PROPERTY_0, PROPERTY_1, PROPERTY_2, PROPERTY_3, PROPERTY_4, PROPERTY_5, PROPERTY_6, PROPERTY_7, PROPERTY_8, PROPERTY_9, PROPERTY_10, PROPERTY_11, PROPERTY_12, PROPERTY_13, PROPERTY_14, PROPERTY_15, PROPERTY_16, PROPERTY_17, PROPERTY_18, PROPERTY_19, PROPERTY_20, PROPERTY_21, PROPERTY_22, PROPERTY_23, PROPERTY_24, PROPERTY_25, PROPERTY_26, PROPERTY_27, PROPERTY_28, PROPERTY_29, PROPERTY_30, PROPERTY_31, PROPERTY_32, PROPERTY_33, PROPERTY_34, PROPERTY_35, PROPERTY_36, PROPERTY_37, PROPERTY_38, PROPERTY_39, PROPERTY_40, PROPERTY_41, PROPERTY_42, PROPERTY_43, PROPERTY_44, PROPERTY_45, PROPERTY_46, PROPERTY_47, PROPERTY_48, PROPERTY_49, PROPERTY_50, PROPERTY_51, PROPERTY_52, PROPERTY_53, PROPERTY_54, PROPERTY_55, PROPERTY_56, PROPERTY_57, PROPERTY_58, PROPERTY_59, PROPERTY_60, PROPERTY_61, PROPERTY_62, PROPERTY_63, PROPERTY_64, PROPERTY_65, PROPERTY_66, PROPERTY_67, PROPERTY_68, PROPERTY_69, PROPERTY_70, PROPERTY_71, PROPERTY_72, PROPERTY_73, PROPERTY_74, PROPERTY_75, PROPERTY_76, PROPERTY_77, PROPERTY_78, PROPERTY_79, PROPERTY_80, PROPERTY_81, PROPERTY_82, PROPERTY_83, PROPERTY_84, PROPERTY_85, PROPERTY_86, PROPERTY_87, PROPERTY_88, PROPERTY_89, PROPERTY_90, PROPERTY_91, PROPERTY_92, PROPERTY_93, PROPERTY_94, PROPERTY_95, PROPERTY_96, PROPERTY_97, PROPERTY_98, PROPERTY_99, PROPERTY_100, RECORD_STATUS, UPDATE_COUNT, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE)
values (35, 27, '', '23', '27', '29', '31, 33', '100000', 'kingnod', '宝安', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'A', 0, 23, to_date('09-03-2012 15:42:25', 'dd-mm-yyyy hh24:mi:ss'), 23, to_date('14-03-2012 10:06:36', 'dd-mm-yyyy hh24:mi:ss'));
insert into org_user_property_data (ID, USER_ID, PROPERTY_0, PROPERTY_1, PROPERTY_2, PROPERTY_3, PROPERTY_4, PROPERTY_5, PROPERTY_6, PROPERTY_7, PROPERTY_8, PROPERTY_9, PROPERTY_10, PROPERTY_11, PROPERTY_12, PROPERTY_13, PROPERTY_14, PROPERTY_15, PROPERTY_16, PROPERTY_17, PROPERTY_18, PROPERTY_19, PROPERTY_20, PROPERTY_21, PROPERTY_22, PROPERTY_23, PROPERTY_24, PROPERTY_25, PROPERTY_26, PROPERTY_27, PROPERTY_28, PROPERTY_29, PROPERTY_30, PROPERTY_31, PROPERTY_32, PROPERTY_33, PROPERTY_34, PROPERTY_35, PROPERTY_36, PROPERTY_37, PROPERTY_38, PROPERTY_39, PROPERTY_40, PROPERTY_41, PROPERTY_42, PROPERTY_43, PROPERTY_44, PROPERTY_45, PROPERTY_46, PROPERTY_47, PROPERTY_48, PROPERTY_49, PROPERTY_50, PROPERTY_51, PROPERTY_52, PROPERTY_53, PROPERTY_54, PROPERTY_55, PROPERTY_56, PROPERTY_57, PROPERTY_58, PROPERTY_59, PROPERTY_60, PROPERTY_61, PROPERTY_62, PROPERTY_63, PROPERTY_64, PROPERTY_65, PROPERTY_66, PROPERTY_67, PROPERTY_68, PROPERTY_69, PROPERTY_70, PROPERTY_71, PROPERTY_72, PROPERTY_73, PROPERTY_74, PROPERTY_75, PROPERTY_76, PROPERTY_77, PROPERTY_78, PROPERTY_79, PROPERTY_80, PROPERTY_81, PROPERTY_82, PROPERTY_83, PROPERTY_84, PROPERTY_85, PROPERTY_86, PROPERTY_87, PROPERTY_88, PROPERTY_89, PROPERTY_90, PROPERTY_91, PROPERTY_92, PROPERTY_93, PROPERTY_94, PROPERTY_95, PROPERTY_96, PROPERTY_97, PROPERTY_98, PROPERTY_99, PROPERTY_100, RECORD_STATUS, UPDATE_COUNT, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE)
values (36, 28, '', '20', '26', '30', '33', '500000', 'kingnode', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'A', 0, 23, to_date('09-03-2012 15:43:47', 'dd-mm-yyyy hh24:mi:ss'), 23, to_date('14-03-2012 10:06:44', 'dd-mm-yyyy hh24:mi:ss'));
insert into org_user_property_data (ID, USER_ID, PROPERTY_0, PROPERTY_1, PROPERTY_2, PROPERTY_3, PROPERTY_4, PROPERTY_5, PROPERTY_6, PROPERTY_7, PROPERTY_8, PROPERTY_9, PROPERTY_10, PROPERTY_11, PROPERTY_12, PROPERTY_13, PROPERTY_14, PROPERTY_15, PROPERTY_16, PROPERTY_17, PROPERTY_18, PROPERTY_19, PROPERTY_20, PROPERTY_21, PROPERTY_22, PROPERTY_23, PROPERTY_24, PROPERTY_25, PROPERTY_26, PROPERTY_27, PROPERTY_28, PROPERTY_29, PROPERTY_30, PROPERTY_31, PROPERTY_32, PROPERTY_33, PROPERTY_34, PROPERTY_35, PROPERTY_36, PROPERTY_37, PROPERTY_38, PROPERTY_39, PROPERTY_40, PROPERTY_41, PROPERTY_42, PROPERTY_43, PROPERTY_44, PROPERTY_45, PROPERTY_46, PROPERTY_47, PROPERTY_48, PROPERTY_49, PROPERTY_50, PROPERTY_51, PROPERTY_52, PROPERTY_53, PROPERTY_54, PROPERTY_55, PROPERTY_56, PROPERTY_57, PROPERTY_58, PROPERTY_59, PROPERTY_60, PROPERTY_61, PROPERTY_62, PROPERTY_63, PROPERTY_64, PROPERTY_65, PROPERTY_66, PROPERTY_67, PROPERTY_68, PROPERTY_69, PROPERTY_70, PROPERTY_71, PROPERTY_72, PROPERTY_73, PROPERTY_74, PROPERTY_75, PROPERTY_76, PROPERTY_77, PROPERTY_78, PROPERTY_79, PROPERTY_80, PROPERTY_81, PROPERTY_82, PROPERTY_83, PROPERTY_84, PROPERTY_85, PROPERTY_86, PROPERTY_87, PROPERTY_88, PROPERTY_89, PROPERTY_90, PROPERTY_91, PROPERTY_92, PROPERTY_93, PROPERTY_94, PROPERTY_95, PROPERTY_96, PROPERTY_97, PROPERTY_98, PROPERTY_99, PROPERTY_100, RECORD_STATUS, UPDATE_COUNT, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE)
values (37, 29, '2012-03-15', '21', '27', '28', '33', '', 'kingnod', '南山西丽', '2012-03-31', '2', '4', '', '20', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'A', 0, 23, to_date('12-03-2012 14:45:27', 'dd-mm-yyyy hh24:mi:ss'), 23, to_date('17-03-2012 15:36:27', 'dd-mm-yyyy hh24:mi:ss'));