oracle分割问题

Question

--这些是外面传入的字符串 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,  字符串模糊匹配

Answer 1

SQL> drop table student;Table droppedSQL> create table student
  2  (
  3         stuid number,
  4         stuname varchar2(100)
  5  );Table createdSQL> 
SQL> insert into student values(1001,'yuanpan/32489');1 row insertedSQL> insert into student values(2001,'zhoujielun/32569');1 row insertedSQL> insert into student values(3001,'zhangbo/35698');1 row insertedSQL> insert into student values(4001,'yuanpanaa/45687');1 row insertedSQL> insert into student values(5001,'zhoujielunbb/56984');1 row insertedSQL> insert into student values(6001,'zhangbovv/39741');1 row insertedSQL> commit;Commit completeSQL> select wm_concat(stuid) from student where instr('yuanpan/32489,zhoujielun/32569,zhangbo/35698',stuname)>0;WM_CONCAT(STUID)
--------------------------------------------------------------------------------
1001,2001,3001SQL>  drop table student;Table droppedSQL> 

Answer 2

select stuid from student 
where instr('yuanpan/32489,zhoujielun/32569,zhangbo/35698',stuname)>0
     STUID
----------
      1001
      2001
      3001

Answer 3

额 不好意思 开始没有说清楚  不是这样的。是要模糊匹配 
--这些是外面传入的字符串 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,  字符串模糊匹配

Answer 4

--这样很好把建表以及插入的代码全部贴出来了 和符合版主的规范 加一分
  1  select replace(wm_concat(stuid),',','、') from student
  2* where  regexp_like('yuanpan/32489,zhoujielun/32569,zhangbo/35698',stuname)
SQL> /REPLACE(WM_CONCAT(STUID),',','、')
--------------------------------------------------------------------------------
1001、2001、3001SQL> 

Answer 5

select case
         when wm_concat(id) is null then
          'Not Found'
         else
          ',' || wm_concat(id) || ','
       end
  from hw_acl_user_t
 where instr(lower('...yuanpan 0032489,zhaoxiaoming 001017555,fengdan 00126235'),
                    lower(full_name)) > 0;
 
 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));
--用instr 和 regexp_like 哪种比较好？ 两种得出的结果没有区别