Oracle SQL：检查逗号分隔字符串中是否存在指定的单词

Question

我有一个SQL函数，它使我返回一串逗号分隔的国家代码。

我已经在另一个表中配置了一些特定代码，并且以后可能会删除或添加更多。

我想检查逗号分开的字符串是否只是这些特定国家代码的组合。也就是说，如果该字符串具有除指定的代码以外的单个国家代码，则应返回true。

假设我在静态数据表GB和CH中配置了两个行。然后我需要以下结果：

的字符串	函数结果
gb	false
false	false
gb，ch	false
ch，gb	false
gb，fr	true
fr，es	true
es，ch	true
ch，gb，gb，es	true

我在Oracle 19c上，只能使用可用于此版本的功能。另外，我希望它得到优化。就像我可以检查字符串中的值数，然后计算每个特定代码。如果不匹配，则显然存在一些其他代码。但是我不想使用循环。

有人可以建议我一个更好的选择。

Answer 1

假设静态表中的所有国家代码以及逗号分隔字符串中的所有令牌始终都是两个字符串，您可以做这样的事情：

with
  static_data(country_code) as (
    select 'GB' from dual union all
    select 'CH' from dual
  )
, sample_inputs(string_from_function) as (
    select 'GB'       from dual union all
    select 'CH'       from dual union all
    select 'GB,CH'    from dual union all
    select 'CH,GB'    from dual union all
    select 'GB,FR'    from dual union all
    select 'FR,ES'    from dual union all
    select 'ES,CH'    from dual union all
    select 'CH,GB,ES' from dual
  )
select string_from_function,
       case when regexp_replace(string_from_function,
                   ',| |' || (select listagg(country_code, '|')
                                       within group (order by null)
                              from   static_data))
                 is null then 'false' else 'true' end as result
from   sample_inputs
;

输出：

STRING_FROM_FUNCTION   RESULT  
---------------------- --------
GB                     false   
CH                     false   
GB,CH                  false   
CH,GB                  false   
GB,FR                  true    
FR,ES                  true    
ES,CH                  true    
CH,GB,ES               true

正则表达式替代逗号，空间，每两个 - 带有 null 的静态数据表中的字母乡村代码。如果整个过程的结果是 null ，则CSV中的所有编码都在静态表中；这就是您需要测试的。

假设保证了像GBCH这样的令牌（对于像“大障碍国家”这样的国家）不会被误认为是可以的，因为GB和CH分别可以。

Answer 2

您可以将CSV列转换为表格并使用。例如

with tbl(id,str) as
( 
SELECT 1,'GB,CH' FROM DUAL UNION ALL
SELECT 2,'GB,CH,FR' FROM DUAL UNION ALL
SELECT 3,'GB' FROM DUAL 
),
countries (code) as
(SELECT 'GB' FROM DUAL UNION ALL
 SELECT 'CH' FROM DUAL 
)

select t.* ,
     case when exists (
        select 1 
        from xmltable(('"' || REPLACE(str, ',', '","') || '"')) s  
        where trim(s.column_value) not in (select code from countries)
      ) 
      then 'true' else 'false' end flag
from tbl t

Answer 3

一种选择是逐一匹配国家代码，然后确定是否存在从提供的文字作为参数中的额外的非匹配国家 /地区。

以下一个具有完整加入的人将考虑上面的逻辑

WITH
  FUNCTION with_function(i_countries VARCHAR2) RETURN VARCHAR2 IS
    o_val VARCHAR2(10);
  BEGIN
     SELECT CASE WHEN SUM(NVL2(t.country_code,0,1))=0 THEN 'false' 
                 ELSE 'true'
                  END
       INTO o_val           
       FROM (SELECT DISTINCT REGEXP_SUBSTR(i_countries,'[^ ,]+',1,level) AS country
               FROM dual
            CONNECT BY level <= REGEXP_COUNT(i_countries,',')+1) tt
       FULL JOIN t
              ON tt.country = t.country_code;      
    RETURN o_val;
  END;
SELECT with_function(<comma-seperated-parameter-list>) AS result
  FROM dual

Answer 4

这是一个解决方案

 使用CTE为
（选择不同的
s，regexp_substr（s，'[^，]+'，1，级别）代码来自字符串
    
 通过Regexp_substr（s，'[^，]+'，1，1，级别）连接不是null
）
选择 
s字符串，最小（存在的情况
  （从国家 /地区选择 *
   cod =代码）然后'yes'
   否则'no'end）all_found
来自CTE
组
s命令s;
 

字符串| all_found
：----- | ：---------
ch |是的      
CH，GB |是的      
ES |不       
Es，Ch |不       
fr |不       
GB |是的      
GB，ch |是的      
GB，ES |不       

db<>fiddle here

Answer 5

如果静态表中有少数值，那么最简单的方法可能不是将值与函数拆分，而是使用静态表中的所有值组合，使用：

SELECT SUBSTR(SYS_CONNECT_BY_PATH(value, ','), 2) AS combination
FROM   static_table
CONNECT BY NOCYCLE PRIOR value != value;

对于示例数据：

CREATE TABLE static_table(value) AS
SELECT 'GB' FROM DUAL UNION ALL
SELECT 'CH' FROM DUAL;

输出：输出：

组合
GB
GB，CH
ch
CH，GB

，然后您可以将简单的案例表达到字符串输出到组合：

SELECT function_value,
       CASE
       WHEN function_value IN (SELECT SUBSTR(SYS_CONNECT_BY_PATH(value, ','), 2)
                               FROM   static_table
                               CONNECT BY NOCYCLE PRIOR value != value)
       THEN 'false'
       ELSE 'true'
       END AS not_matched
FROM   string_from_function;

对于示例数据：输出：

CREATE TABLE string_from_function(function_value) AS
SELECT 'GB' FROM DUAL UNION ALL
SELECT 'CH' FROM DUAL UNION ALL
SELECT 'GB,CH' FROM DUAL UNION ALL
SELECT 'CH,GB' FROM DUAL UNION ALL
SELECT 'GB,FR' FROM DUAL UNION ALL
SELECT 'FR,ES' FROM DUAL UNION ALL
SELECT 'ES,CH' FROM DUAL UNION ALL
SELECT 'CH,GB,ES' FROM DUAL;

输出：

function_value	not_matched
gb	false
ch	false
gb，ch	false
ch，gb	false
gb，fr	true
fr，es	true
es，ch	true
ch，gb，es	true

db＆lt;＆gt; fiddle here