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

cdhhb 2012-03-21 04:26:34

写一个sql函数：
testFunction(arg1,arg2);

当调用 testFunction('31,32,33','22,32,33,42')时，返回两个串的交集：32,33

请问应该如何实现？请高手指点，谢谢！
...全文
203 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
raymonshi 2012-03-21
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 xpingping 的回复:]

SQL code
create or replace function testFunction
(
Str1 in varchar2,
Str2 in varchar2
) return varchar2 is
result varchar2(4000);
begin
select wm_concat(rn)
into result
f……
[/Quote]
正解，支持。
cdhhb 2012-03-21
  • 打赏
  • 举报
 回复
解决了！太感谢两位的热情帮助了！！！
mailking 2012-03-21
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 cdhhb 的回复:]

直接调用函数是正常的
select testFunction('31,32,33','31,32,34,35') from dual;
这样是正确的，返回值：31,32

把表的字段作为参数放进去，就不行了。
select testFunction(property_4,'33'),id from org_user_property_data;
这样查出来的数据不正确。请问是什……
[/Quote]


你的数据的字符串有空格存在吧
cdhhb 2012-03-21
  • 打赏
  • 举报
 回复
表数据
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'));
cdhhb 2012-03-21
  • 打赏
  • 举报
 回复
表结构
-- 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
);
cdhhb 2012-03-21
  • 打赏
  • 举报
 回复
直接调用函数是正常的
select testFunction('31,32,33','31,32,34,35') from dual;
这样是正确的，返回值：31,32

把表的字段作为参数放进去，就不行了。
select testFunction(property_4,'33'),id from org_user_property_data;
这样查出来的数据不正确。请问是什么原因呢？

xpingping 2012-03-21
  • 打赏
  • 举报
 回复 
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;
xpingping 2012-03-21
  • 打赏
  • 举报
 回复 
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
oracle 判断A字符串数组和B字符串数组有交集的字符串个数函数
create or replace function ...--新增判断A字符串数组和B字符串数组有交集的字符串个数函数。--判断A字符串数组和B字符串数组有交集的字符串个数。晚点等我试试管道函数看看效率是否有提升。
oracle2个字符串求交集,Oracle 两个逗号分割的字符串,获取交集、差集(sql实现过程解析)...
Oracle数据库的两个字段值为逗号分割的字符串，例如：字段A值为“1,2,3,5”，字段B为“2”。想获取两个字段的交集(相同值)2，获取两个字段的差集(差异值)1,3,5。一、最终实现的sql语句1、获取交集(相同值)：select ...
sql语句交集情况的查询技巧
开发过程中涉及到了查询字符串交集的情况，不会写这样的sql，上网一搜，还真有例子。现在分享给大家。 也许某一天你就会用到，你懂的。 交集 表的字段就是 name no  a 2,9  b 8,10  字符串是str="0,1...
PostgreSQL使用OVERLAPS函数判断两个时间段的交集
PostgreSQL使用OVERLAPS函数判断两个时间段的交集
Oracle 两个字符串按分隔符取交集
最近在项目中碰到一个问题，有个表的字段是按逗号分割存入的字符串，然后页面需要对这个字段进行...变通了下，写一个函数。如下： CREATE OR REPLACE FUNCTION GET_INTERSECT(V_STR1 VARCHAR2, V_STR2 VARCHAR2, .
基础和管理

17,377

社区成员

95,128

社区内容

发帖
与我相关
我的任务
社区描述
Oracle 基础和管理
社区管理员
  • 基础和管理社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章