根据文本选择连接字段的值Oracle SQL [英] Concatenate values of field depending on text selection Oracle SQL

问题描述

我有一个表a_x，其中有A列和B列，A列为CLOB格式，B列的编号为(10):

I have a table a_x with the columns A and B, column A is in CLOB format, column B number(10):

A                            | B                                    
-----------------------------|-------
atveroeosipsumloremipsumdolor| 1
stetclitakasdtest            | 2
noseatakimataatveroeosipsum  | 3
loremipsumdolor              | 4
consetetursadipscingelitr    | 5

我想生成此表以找出哪个ID与某些子字符串一起出现:

I would like to produce this table, in order to find out, which ID goes with certain substrings:

A                            | IDs                                    
-----------------------------|-------
atveroeosipsum               | 1,3
test                         | 2
stetclitakasd                | 2
noseatakimata                | 3
loremipsumdolor              | 1,4
consetetursadipscingelitr    | 5

我尝试了以下代码:

create table a_y 
as
select a 
  from a_x where a contains('atveroeosipsum', 'test'
                  , 'stetclitakasd', 'noseatakimata'
                  , 'loremipsumdolor', 'consetetursadipscingelitr')

alter table a_y
add ids varchar2(2000); 

由于00920. 00000 - "invalid relational operator"，该代码无法正常工作. 我认为不可能以CLOB格式搜索文本.当列A为varchar格式时，如何生成第二个表?

The code is not working because of 00920. 00000 - "invalid relational operator". I think it is not possible to search for text in CLOB format. How can I produce the second table, when column A is in varchar format?

更新:mathguy的代码有效.我想使用一个名为table_expressions的表，其中包含所需的表达式.我创建了此表，该表仅包含一列(结果表"的列A).

UPDATE: The code from mathguy works. I wanted to use a table called table_expressions, which contains the desired expressions. I created this table, which contains only one column (column A of the "result table").

从mathguy修改的代码:

The modified code from mathguy:

create table a_y 
  as 
  with 
  input_strings ( a ) as ( 
select column_value from table_expressions 
  ) 
select t2.a, listagg(t1.b, ',') within group (order by t1.b) 
 as ids from a_x t1 join input_strings t2 on t1.a like '%' || t2.a || '%' 
 group by t2.a 

另请参阅问题 XMLAGG

推荐答案

用于字符串比较的正确运算符是LIKE.请注意，它适用于CLOB，而不仅适用于VARCHAR2.

The correct operator for string comparisons is LIKE. Note that it works for CLOBs, not just for VARCHAR2.

在下面的示例中，我使用一种特定的方法动态创建了输入字符串表.还有其他几种方法-使用您熟悉的方法.

In the example below I create the table of input strings on the fly using one particular method. There are several other methods - use whichever you are familiar with.

with
     a_x ( a, b ) as (
       select to_clob('atveroeosipsumloremipsumdolor'), 1 from dual union all
       select to_clob('stetclitakasdtest')            , 2 from dual union all
       select to_clob('noseatakimataatveroeosipsum')  , 3 from dual union all
       select to_clob('loremipsumdolor')              , 4 from dual union all
       select to_clob('consetetursadipscingelitr')    , 5 from dual
     ),
     input_strings ( str ) as (
       select column_value
       from   table ( sys.odcivarchar2list ( 'atveroeosipsum', 'test', 'stetclitakasd',
                                             'noseatakimata', 'loremipsumdolor',
                                             'consetetursadipscingelitr'
                                           )
                    )                    
     )
select   t2.str, listagg(t1.b, ',') within group (order by t1.b) as ids
from     a_x t1 
         join input_strings t2 on t1.a like '%' || t2.str || '%'
group by t2.str
;

STR                        IDS
-------------------------  ---
atveroeosipsum             1,3
consetetursadipscingelitr  5
loremipsumdolor            1,4
noseatakimata              3
stetclitakasd              2
test                       2

这篇关于根据文本选择连接字段的值Oracle SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！