如何通过Oracle regexp_replace从空格分隔的列表中删除重复项? [英] How to remove duplicates from space separated list by Oracle regexp_replace?

问题描述

我有一个名为"A B A A C D"的列表.我的预期结果是"A B C D".到目前为止，我还没有发现网络

regexp_replace(l_user ,'([^,]+)(,[ ]*\1)+', '\1');

表达.但这是用于，分隔列表.为了使其成为空格分隔的列表，需要进行哪些修改.无需考虑订单.

解决方案

如果我很了解，您不仅需要用空格替换'，'，而且还可以更聪明地删除重复项.

如果我修改该表达式以使用空格而不是'，'，我将得到

select regexp_replace('A B A A C D' ,'([^ ]+)( [ ]*\1)+', '\1') from dual

给出'A B A C D'，而不是您所需要的.

获得所需结果的一种方法可能如下:

with string(s) as ( select 'A B A A C D' from dual)    
    select listagg(case when rn = 1 then str end, ' ') within group (order by lev)
    from (
            select str,  row_number() over (partition by str order by 1) rn, lev
            from (
                SELECT trim(regexp_substr(s, '[^ ]+', 1, level)) str,
                       level as lev
                  FROM string
                CONNECT BY instr(s, ' ', 1, level - 1) > 0
                )
         )

这里的主要问题是我无法构建用于检查非相邻重复项的正则表达式，因此我需要分割字符串，检查重复项，然后再次聚合非重复值，以保持顺序. /p>

如果您不介意结果字符串中标记的顺序，则可以简化以下操作:

with string(s) as ( select 'A B A A C D' from dual)
select listagg(str, ' ') within group (order by 1)
from (
        SELECT distinct trim(regexp_substr(s, '[^ ]+', 1, level)) as str
          FROM string
        CONNECT BY instr(s, ' ', 1, level - 1) > 0
     )

I have a list called 'A B A A C D'. My expected result is 'A B C D'. So far from web I have found out

regexp_replace(l_user ,'([^,]+)(,[ ]*\1)+', '\1');

Expression. But this is for , separated list. What is the modification need to be done in order to make it space separated list. no need to consider the order.

解决方案

If I understand well you don't simply need to replace ',' with a space, but also to remove duplicates in a smarter way.

If I modify that expression to work with space instead of ',', I get

select regexp_replace('A B A A C D' ,'([^ ]+)( [ ]*\1)+', '\1') from dual

which gives 'A B A C D', not what you need.

A way to get your needed result could be the following, a bit more complicated:

with string(s) as ( select 'A B A A C D' from dual)    
    select listagg(case when rn = 1 then str end, ' ') within group (order by lev)
    from (
            select str,  row_number() over (partition by str order by 1) rn, lev
            from (
                SELECT trim(regexp_substr(s, '[^ ]+', 1, level)) str,
                       level as lev
                  FROM string
                CONNECT BY instr(s, ' ', 1, level - 1) > 0
                )
         )

My main problem here is that I'm not able to build a regexp that checks for non adjacent duplicates, so I need to split the string, check for duplicates and then aggregate again the non duplicated values, keeping the order.

If you don't mind the order of the tokens in the result string, this can be simplified:

with string(s) as ( select 'A B A A C D' from dual)
select listagg(str, ' ') within group (order by 1)
from (
        SELECT distinct trim(regexp_substr(s, '[^ ]+', 1, level)) as str
          FROM string
        CONNECT BY instr(s, ' ', 1, level - 1) > 0
     )

这篇关于如何通过Oracle regexp_replace从空格分隔的列表中删除重复项?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！