Oracle 单字段中的多个部分搜索

创建表t (id号，名称varchar2(30))

插入t (111，'mgr_123名称123 ');

插入t (123，'silvaji_521主234 ');

插入t (786，'rajini_786 sounth 111 ');

插入t (678，'vikram_333 nila 532441 ');

插入t (987，'surya_55 jo 11231 ');

我们可以在SQL中的单个查询中传递多个部分搜索值吗？或者我们可以在PLSQL中做？

我的过程只有一个输入值正在传递，基于我必须返回表值。该输入值可以提供多个部分值。你能提供解决方案吗？

输入值 = mgr，soun，5324，

是否可以像下面这样创建查询？
从T中选择 *
其中UPPER (名称) 像UPPER(:input_Value);

我需要一个如下所示的输出

ID名称
---- ------
111 MGR_123名称123
786 Rajini_786 sounth 111
678维克拉姆 _ 333尼拉532441

谢谢
JPartheeban

你检查了那个测试用例，对吗？:-)

SQL> insert into t (111, 'MGR_123 NAME 123');
insert into t (111, 'MGR_123 NAME 123')
               *
ERROR at line 1:
ORA-00928: missing SELECT keyword


SQL> insert into t (123, 'Silvaji_521 main 234');
insert into t (123, 'Silvaji_521 main 234')
               *
ERROR at line 1:
ORA-00928: missing SELECT keyword


SQL> insert into t (786, 'Rajini_786 sounth 111');
insert into t (786, 'Rajini_786 sounth 111')
               *
ERROR at line 1:
ORA-00928: missing SELECT keyword


SQL> insert into t (678, 'vIKRAm_333 nila 532441');
insert into t (678, 'vIKRAm_333 nila 532441')
               *
ERROR at line 1:
ORA-00928: missing SELECT keyword


SQL> insert into t (987, 'SURYA_55 jo 11231');
insert into t (987, 'SURYA_55 jo 11231')
               *
ERROR at line 1:
ORA-00928: missing SELECT keyword

无论如何 .... 您可以通过多种方式将字符串转换为一组行，例如:

SQL> variable str varchar2(100)
SQL> exec :str := 'mgr,soun,5324,';

PL/SQL procedure successfully completed.

SQL>
SQL> select substr(:str,
  2              loc,nvl(
  3              lead(loc) over ( order by loc ) – loc-1,
  4              length(:str)-loc)
  5             ) list_as_rows
  6      from (
  7        select distinct (instr(','||:str,',',1,level)) loc
  8        from dual
  9        connect by level <= length(:str)-length(replace(:str,','))
 10       );

LIST_AS_ROWS
---------------------------------------------------------------------
mgr
soun
5324

一旦你做到了，这只是一个正常的连接

SQL> create table t (id number, name varchar2(30));

Table created.

SQL>
SQL> insert into t values(111, 'MGR_123 NAME 123');

1 row created.

SQL> insert into t values(123, 'Silvaji_521 main 234');

1 row created.

SQL> insert into t values(786, 'Rajini_786 sounth 111');

1 row created.

SQL> insert into t values(678, 'vIKRAm_333 nila 532441');

1 row created.

SQL> insert into t values(987, 'SURYA_55 jo 11231');

1 row created.

SQL>
SQL> variable str varchar2(100)
SQL> exec :str := 'mgr,soun,5324,';

PL/SQL procedure successfully completed.

SQL>
SQL> with x as
  2  (
  3  select substr(:str,
  4              loc,nvl(
  5              lead(loc) over ( order by loc ) – loc-1,
  6              length(:str)-loc)
  7             ) list_as_rows
  8      from (
  9        select distinct (instr(','||:str,',',1,level)) loc
 10        from dual
 11        connect by level <= length(:str)-length(replace(:str,','))
 12       )
 13  )
 14  select *
 15  from t, x
 16  where instr(name,list_as_rows) > 0;

        ID NAME                           LIST_AS_ROWS
---------- ------------------------------ --------------------------------------------------------------------------------------------------------------------------------
       786 Rajini_786 sounth 111          soun
       678 vIKRAm_333 nila 532441         5324

2 rows selected.

SQL>
SQL>
SQL>
SQL>

如果需要不区分大小写的检查，请在适当的地方使用上/下