Oracle从不同的行中选择相互的子串

问题描述

我想在 ORACLE 中返回新表，其中在 'col' 列中具有相同值的所有行组合在一起，并且当不同的字符将被替换为 '...'

I would like to return new table in ORACLE where all the rows that have same values in 'col' column group together and the 'description' column will contain only the mutual sub strings when the different characters will replaced by '...'

我该怎么做?我可以请你帮忙吗?

How can I do that? May I get your help please?

示例:

col description
1   Today is a good day
1   Today is perfect day
2   Hello world
2   Hello I'm here
3   Hi

结果:

col description
1   Today is …
2   Hello…
3   Hi

谢谢！

推荐答案

为了实现您想要的，首先您需要知道如何在 Oracle 中找到常见的字符串开头:

In order to achieve what you want, first you will need to know how common string start can be found in Oracle:

with rws as ( 
  -- generate rows up to the length of the longest string 
  select rownum r from dual 
  connect by level <= (select max(length(str)) from strings) 
) 
select distinct s  
from ( 
  select str, max(subp) s  
  from ( 
    select str,  
           substr(str, 1, rws.r) subp 
    from   strings s1 
    cross join rws 
    where  r < length(str) 
    and    exists ( 
      -- check whether there's another string matching the first N chars 
      select * from strings s2 
      where  s1.str <> s2.str 
      and    substr(s1.str, 1, rws.r) = substr(s2.str, 1, rws.r) 
    ) 
  )  
  group by str 
)

取自 https://livesql.oracle.com/apex/livesql/file/content_CC83ZWPCPESEDTADIFOSB1EXI.html

当然，您需要将其应用到您的表中.差异:

Of course, you will need to apply this to your table. Differences:

• 您将需要使用您的列
• 您需要按列分组

这篇关于Oracle从不同的行中选择相互的子串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！