A表有一列,值是:“,pdpz,jz_pzpd,jz_pzjj,ywcx,cx_kcxx,cx_pzcx,”(字符串用逗号隔开,不包括双引号)。A表这一列每个字符串都是B表中的idA表:
MENU_QX
-------------------------------------------
,jz_pzjj,ywcx,cx_kcxx,cx_pzcx,B表:
MENU_ID
------------
pdpz
jz_pzpd
jz_pzjj
ywcx
cx_kcxx
cx_pzcx
fx_unsj
fx_shpz
fx_lfcx数据我只写出了一部分,解决问题应该够用了,现在我想要查询B表中A表字符串包含的那几行,怎么查?不知道我描述的清楚么,结果是:
B表:
MENU_ID
----------
jz_pzjj
ywcx
cx_kcxx
cx_pzcx求大神指导!!谢谢!!字符串,截取
MENU_QX
-------------------------------------------
,jz_pzjj,ywcx,cx_kcxx,cx_pzcx,B表:
MENU_ID
------------
pdpz
jz_pzpd
jz_pzjj
ywcx
cx_kcxx
cx_pzcx
fx_unsj
fx_shpz
fx_lfcx数据我只写出了一部分,解决问题应该够用了,现在我想要查询B表中A表字符串包含的那几行,怎么查?不知道我描述的清楚么,结果是:
B表:
MENU_ID
----------
jz_pzjj
ywcx
cx_kcxx
cx_pzcx求大神指导!!谢谢!!字符串,截取
p_delimiter IN VARCHAR2) RETURN arr_var
/*
功能:
Created by :BYF
Created Date :
*/
IS
j INT := 0;
i INT := 1;
len INT := 0;
len1 INT := 0;
str VARCHAR2(32767);
str_split arr_var := arr_var();
BEGIN
len := length(p_str);
len1 := length(p_delimiter); WHILE j < len LOOP
j := instr(p_str, p_delimiter, i);
IF j = 0 THEN
j := len;
str := substr(p_str, i);
IF length(str) <> 0 THEN
str_split.EXTEND;
str_split(str_split.COUNT) := str;
END IF;
IF i >= len THEN
EXIT;
END IF;
ELSE
str := substr(p_str, i, j - i);
i := j + len1;
IF length(str) <> 0 THEN
str_split.EXTEND;
str_split(str_split.COUNT) := str;
END IF;
END IF;
END LOOP; RETURN str_split;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_split;SQL> select column_value from table(cast(f_split(',pdpz,jz_pzpd,jz_pzjj,ywcx,cx_kcxx,cx_pzcx,', ',') AS arr_var));
COLUMN_VALUE
--------------------------------------------------------------------------------
pdpz
jz_pzpd
jz_pzjj
ywcx
cx_kcxx
cx_pzcx然后和另外一个表关联下即可。
with tbA as
(
select ',jz_pzjj,ywcx,cx_kcxx,cx_pzcx,' MENU_QX from dual
),tbB as
(
select 'pdpz' MENU_ID from dual union all
select 'jz_pzpd' MENU_ID from dual union all
select 'jz_pzjj' MENU_ID from dual union all
select 'ywcx' MENU_ID from dual union all
select 'cx_kcxx' MENU_ID from dual union all
select 'cx_pzcx' MENU_ID from dual union all
select 'fx_unsj' MENU_ID from dual union all
select 'fx_shpz' MENU_ID from dual union all
select 'fx_lfcx' MENU_ID from dual
)select b.MENU_ID
from tbB b,
(
select distinct regexp_substr(trim(',' from MENU_QX), '[^,]+',1,level) c1
from tbA
connect by level <= length(trim(',' from MENU_QX)) - length(replace(trim(',' from MENU_QX),',','')) + 1
) a where a.c1 = b.MENU_ID MENU_ID
--------------------
1 jz_pzjj
2 ywcx
3 cx_kcxx
4 cx_pzcx