--速度还是很慢，只是写法简单点
select 
t.tm,
(case when instr(t.tm,'资料')>0 then -207 
when instr(t.tm,'资')>0 and instr(t.tm,'料')>0 then -209
else -321 end) code,
t.id 
from "table" t where t.tm like '%资%' or t.tm like '%料%';

------解决方案--------------------
这一句就能得出3个合并的结果了
select t.tm,t.id from table t where t.tm like '%资%' or t.tm like '%料%'


------解决方案--------------------
四楼正解。

------解决方案--------------------

试试这种

with t as (
select '资料' as fname from dual
union all
select 'aa资料bb' from dual
union all
select 'aa资 料bb' from dual
union all  
select 'aa资c 料bb' from dual
union all
select 'aa资cd料bb' from dual
union all  
select 'aaccbb' from dual
)
select * from t
where regexp_like(fname,'资[[:alnum:]]*|[[:space:]]料')
--[[:alnum:]]*：任意字母或者数字0次或多次
--[[:space:]]任意白字符
FNAME     
----------  
资料      
aa资料bb  
aa资 料bb 
aa资c 料bb  
aa资cd料bb

------解决方案--------------------
探讨

SQL code

--速度还是很慢，只是写法简单点
select 
t.tm,
(case when instr(t.tm,'资料')>0 then -207 
when instr(t.tm,'资')>0 and instr(t.tm,'料')>0 then -209
else -321 end) code,
t.id 
from "table" t where t.tm l……