将子字符串与多列进行比较

Question

我有一个表，它有20个类似的文本属性列，text1..text20。这些列属于CLOB类型。我正在查找其中一个文本属性列包含特定短语的行，例如"%失业%"。我需要知道两件事，哪行匹配，哪列匹配。我以为我可以用ANY作为起点，但我遇到了问题。

ANY语句似乎不适用于"%"。例如，

select * from emp where 'BLAKE' = ANY(ename, job); -- Returns Data

但是

select * from emp where '%BLAKE%' = ANY(ename, job) -- No Data Found

这样做的正确方法是什么？伪代码是…

Select name, addr, 
which_column_matched(%unemployed%, text1..text20),
text1..text20
from table
where %unemployed% = ANY(text1..text20);

Answer 1

在Oracle中，可以使用unpivot进行此操作。它仍然需要您枚举所有列，但语法非常简洁。

如果您希望每列有一条匹配的记录：

select *
from emp unpivot (col for src in (text1, text2, text3))
where col like '%unemployed%'

如果您想要一个带有匹配列列表的附加列，则可以聚合结果集：

select ename, listagg(src, ', ')
from emp unpivot (col for src in (text1, text2, text3))
where col like '%unemployed%'
group by ename

Answer 2

您可以使用子查询来识别匹配的第一列，然后返回：

select t.*
from (select t.*,
(case when text1 like '%unemployed%' then 'text1'
when text2 like '%unemployed%' then 'text2'
. . .
when text20 like '%unemployed%' then 'text20'
end) as col_match
from t
) t
where col_match is not null;

Answer 3

我一直担心Oracle如何处理CLOB数据，所以这里有一个测试表明Pivot解决方案应该能做到这一点。

drop table emptest;
-- Assuming we are using the venerable EMP table
create table emptest as select * from emp;
alter table emptest add(
text1 CLOB,
text2 CLOB,
text3 CLOB
)
/
declare
v_text clob;
begin
-- set one column to a length well beyond 16k but below 32k, max VARCHAR2 for PL/SQL
v_text := lpad('X', 16000, 'X')||' unemployed ' || lpad('X', 10000, 'X');
update emptest set text2 = v_text where ename = 'SMITH';
-- set others to short values
v_text := 'an unemployed salesman in text 1';
update emptest set text1 = v_text where ename = 'TURNER';
v_text := 'an unemployed manager in text 3';
update emptest set text3 = v_text where ename = 'JONES';
commit;
end;
/
declare
v_clob clob;
begin
-- Set a field to an absurdly long value, with the match value way beyond 32k.
update emptest set text1 = empty_clob() where ename = 'SMITH' returning text1 into v_clob;
for i in 1..10000 loop
dbms_lob.writeappend(v_clob, 36, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890');
end loop;
dbms_lob.writeappend(v_clob, 18, 'unemployed manager');
commit;
end;
/
select empno, ename, clob_name, clob_value, length(clob_value) clob_length
from emptest unpivot (clob_value for clob_name in (text1, text2, text3))
where clob_value like '%unemployed%'
/

这样做的结果将是：

EMPNO ENAME   CLOB_NAME CLOB_VALUE  CLOB_LENGTH
----- ------- --------- ----------- -----------
7566  JONES   TEXT3     <excluded>  31
7369  SMITH   TEXT1     <excluded>  360018
7369  SMITH   TEXT2     <excluded>  26012
7844  TURNER  TEXT1     <excluded>  32

关键的是Oracle在处理SMITH的TEXT1时如何处理LIKE关键字：请注意，该列的长度大于360k个字符。我们尝试在CLOB数据类型中使用的许多标准语法之所以有效，只是因为Oracle将CLOB强制转换为VARCHAR2，但这有固有的长度限制。

正如这个测试所显示的，LIKE比较确实适用于胖CLOB值——至少在我测试它的Oracle12c中是这样

如果您尝试显示匹配的实际内容，情况会有所不同：如果您处理的是长CLOB值，则需要熟悉DBMS_LOB包及其子程序(如DBMS_LOB.INSTR和DBMS_LOB.SUBSTR(。