时间戳

首页 > 解决方案 > 列出函数以查找分隔集之间的差异

sql - 列出函数以查找分隔集之间的差异

问题描述

我有两个分隔列表，如下所示，需要找出发生了什么变化。

示例：（这里 | 是分隔符）

old string: Joe | Public | NY
new string: Joe | Smith  | NY

由于只有列表的第二个成员发生了变化，因此输出应该只显示发生了什么变化，如下所示：

输出：

              - | Smith | -

是否有任何可用的 Oracle 函数或标准包可以比较这两个分隔的字符串/集并确定发生了什么变化？

标签： sqloracleplsql

解决方案

将分隔列表存储在字符串中不是一个好主意。这缺少关系数据库的意义（正如@Tim 在评论中指出的那样）。

如果您在前面的步骤中生成分隔字符串，则返回源数据并直接从该数据开始工作。

如果您真的被这些字符串困扰，有多种方法可以将分隔字符串标记为单个值（作为结果集中的行）。这使用正则表达式将两个字符串一次拆分为两列 - 为此我为每个字符串使用绑定变量来避免重复文字，但您可能会从表列中获取字符串（这需要更多的工作) 或 PL/SQL 变量或其他任何东西：

var old_string varchar2(20);
var new_string varchar2(20);

exec :old_string := 'Joe|Public|NY';
exec :new_string := 'Joe|Smith|NY';

with vals (pos, old_val, new_val) as (
  select level,
    regexp_substr(:old_string, '(.*?)(\||$)', 1, level, null, 1),
    regexp_substr(:new_string, '(.*?)(\||$)', 1, level, null, 1)
  from dual
  connect by level < greatest(regexp_count(:old_string, '(.*?)(\||$)'),
                              regexp_count(:new_string, '(.*?)(\||$)'))
)
select * from vals;

       POS OLD_VAL    NEW_VAL   
---------- ---------- ----------
         1 Joe        Joe       
         2 Public     Smith     
         3 NY         NY

现在很容易比较两列以查看发生了什么变化，然后（如果必须）将它们聚合回单个字符串值：

with vals (pos, old_val, new_val) as (
  select level,
    regexp_substr(:old_string, '(.*?)(\||$)', 1, level, null, 1),
    regexp_substr(:new_string, '(.*?)(\||$)', 1, level, null, 1)
  from dual
  connect by level < greatest(regexp_count(:old_string, '(.*?)(\||$)'),
                              regexp_count(:new_string, '(.*?)(\||$)'))
)
select listagg(case when (old_val is null and new_val is null)
      or old_val = new_val then '-' else new_val end, '|')
    within group (order by pos) as diff
from vals;

DIFF                
--------------------
-|Smith|-

case 表达式决定您看到的是破折号（表示没有变化）还是新值。

这应该处理空值（空元素，即两个相邻的分隔符）；如果发生这种情况，它还将处理不同数量的元素：

exec :old_string := 'Joe|Public|NY||';
exec :new_string := 'Joe|Smith|NY||USA';

... same query ...

DIFF                
--------------------
-|Smith|-|-|USA

但是你真的应该修复你的数据模型......

如果新旧字符串当前来自表中的两列，您可以扩展它以比较多行；connect by您只需要在子句中引用一个非确定性函数：

create table t42 (id, old_string, new_string) as
select 1, 'Joe|Public|NY', 'Joe|Smith|NY' from dual
union all select 2, 'Joe|Public|NY', 'Joe|Smith|NY|USA' from dual;

with vals (id, pos, old_val, new_val) as (
  select id, level,
    regexp_substr(old_string, '(.*?)(\||$)', 1, level, null, 1),
    regexp_substr(new_string, '(.*?)(\||$)', 1, level, null, 1)
  from t42
  connect by id = prior id
  and prior dbms_random.value is not null
  and level < greatest(regexp_count(old_string, '(.*?)(\||$)'),
                       regexp_count(new_string, '(.*?)(\||$)'))
)
select id, listagg(case when (old_val is null and new_val is null)
    or old_val = new_val then '-' else new_val end, '|')
    within group (order by pos) as diff
from vals
group by id
order by id;

        ID DIFF                
---------- --------------------
         1 -|Smith|-           
         2 -|Smith|-|USA

如果它们来自不同的行或不同的表，那么它会更复杂，应该考虑使用@MTOs 方法。

我还应该指出，我假设您的定界符周围的空格是为了使问题中的字符串更容易阅读；如果它们实际上在数据中，则可以调整模式（再次像 @MTO 一样）。

推荐阅读