--这些是外面传入的字符串 yuanpan/32489,zhoujielun/32569,zhangbo/35698
create table student
(
stuid number,
stuname varchar2(100)
);insert into student values(1001,'yuanpan/32489');
insert into student values(2001,'zhoujielun/32569');
insert into student values(3001,'zhangbo/35698');insert into student values(4001,'yuanpanaa/45687');
insert into student values(5001,'zhoujielunbb/56984');
insert into student values(6001,'zhangbovv/39741');---根据传入的字符串动态的获取这样的结果 ,1001,2001,3001, 字符串模糊匹配
这个可以这样解决:
select case
when wm_concat(id) is null then
'Not Found'
else
',' || wm_concat(id) || ','
end
from hw_acl_user_t
where regexp_like(lower('chensong 00105121,chesnyong 50417,chenyonghong'),
lower(full_name));
----============================================================================
----现在我想反过来 。 假如 外面传入的是ID ,1001,3001,5001,注意 字符串是带逗号加ID的。根据传入的字符串动态的获取这样的结果 yuanpan/32489,zhangbo/35698,zhoujielunbb/56984
--试试可以不?,没测试哈
select max(stuname) from(
select stuid,stuname,wm_concat(stuname) over(order by instr(v_id,stuid)) stuname
from student
where instr(v_id,stuid)>0
)
(SELECT rtrim(',1001,3001,5001,', ',') str FROM dual)
SELECT wm_concat(t.stuname)
FROM student t
WHERE t.stuid IN
(SELECT substr(t1.str,
instr(t1.str, ',', 1, LEVEL) + 1,
decode(instr(t1.str, ',', 1, LEVEL + 1),
0,
length(str) + 1,
instr(t1.str, ',', 1, LEVEL + 1)) - instr(t1.str, ',', 1, LEVEL) - 1) s
FROM dual, t1
CONNECT BY LEVEL <= (SELECT length(str) - length(REPLACE(str, ',')) FROM t1));
--已经测试通过
select max(stuname) from(
select wm_concat(stuname) over(order by instr('1001,3001,5001',stuid)) stuname
from student
where instr('1001,3001,5001',stuid)>0
)
select ','||max(stuname)||',' from(
select wm_concat(stuname) over(order by instr('1001,3001,5001',stuid)) stuname
from student
where instr('1001,3001,5001',stuid)>0
)
----这种不行
create table student
(
stuid number,
stuname varchar2(100)
);
---我加了一条ID为1的 ,会把为1的也匹配上。
insert into student values(1,'yuanpanfff/32489');
insert into student values(1001,'yuanpan/32489');
insert into student values(1002,'yuanpansss/32489');
insert into student values(2001,'zhoujielun/32569');
insert into student values(3001,'zhangbo/35698');insert into student values(4001,'yuanpanaa/45687');
insert into student values(5001,'zhoujielunbb/56984');
insert into student values(6001,'zhangbovv/39741');select max(stuname2) from(
select stuid,stuname,wm_concat(stuname) over(order by instr(',1001,2001,',stuid)) stuname2
from student
where instr(',1001,2001,',stuid)>0
)
查询结果为:yuanpan/32489,yuanpanfff/32489,zhoujielun/32569
额。只能用sql查询语句查出来。能不能不写过程?
--这是我之前写的一个函数,你试试能满足你的不?
CREATE OR REPLACE FUNCTION get_ename(id IN VARCHAR2 )
RETURN VARCHAR2
IS
result VARCHAR2(1000); --返回结果
rs VARCHAR2(1000); --employee_name值
num1 NUMBER:=0; --截取多少位
num2 NUMBER:=1; --从哪个位开始取子串,初始从第一位开始
num3 NUMBER:=0; --根据子串中是否存在','来判断是否是最后一个
v_id VARCHAR2(1000); --临时id,查询employee表中对应的employee_name
BEGIN
FOR i IN 1..Length(id) LOOP
IF SubStr(id,i,1) = Chr(44) THEN
num1:=i-num2;
--Dbms_Output.put_line(' i='||i||' num1='||num1||' num2='||num2);
v_id:=SubStr(id,num2,num1);
--Dbms_Output.put_line(' v_id='||v_id);
num2:=i+1;
--Dbms_Output.put_line(' num2='||num2); SELECT stuname INTO rs FROM student WHERE stuid=v_id;
result:=result||rs||',' ;
--Dbms_Output.put_line(' rs='||rs||' result='||result);
--Dbms_Output.put_line(' ===================');
--判断是不是最后一个v_id
SELECT InStr(SubStr(id,num2),',') INTO num3 FROM dual ; IF num3=0 THEN
--Dbms_Output.put_line(' i='||i||' num1='||num1||' num2='||num2||' id='||id||' num3='||num3);
SELECT stuname INTO rs FROM student WHERE stuid=SubStr(id,num2);
result:=result||rs||',' ;
END IF; END IF ;
END LOOP;
RETURN RTrim(result,','); --去掉最后一个,
EXCEPTION WHEN OTHERS THEN
Raise_Application_Error(-20001,'出现错误!'||sqlerrm);
END;--测试:
SELECT get_ename('1001,3001,5001') stuname FROM dual;
四楼的tangren 这种方法不错。