Oracle函数返回字符串之间的相似性 [英] Oracle Function to return similarity between strings

问题描述

我有一个有趣的问题，想知道 oracle 是否有内置函数来执行此操作，或者我需要在 plsql 中找到一种快速的方法来执行此操作.

I have an interesting problem and am wondering if oracle has a built-in function to do this or I need to find a fast way to do it in plsql.

取 2 个字符串:

  s1 = 'abc def hijk'
  s2 = 'abc def iosk'

该函数需要返回abc def，因为到那时为止，字符串完全相同.

The function needs to return abc def because the strings are exactly the same up to that point.

另一个例子:

  s1 = 'abc def hijk www'
  s2 = 'abc def iosk www'

函数需要返回abc def.

我能想到的唯一方法是循环遍历 string1 并将每个字符与 substr() 再次与字符串 2 的 substr 进行比较.

The only way I can think of doing this is loop through string1 and compare each character with substr() again the substr of string 2.

只是想知道 Oracle 是否有内置的东西.性能非常重要.

Just wondering if Oracle's got something built-in. Performance is pretty important.

推荐答案

在重新阅读您的问题后，这将是您真正想要的:

After re-reading your question, here would be what you really wanted:

with cte1 as  (
    select 1 id, 'abc def hijk www' str from dual
    union all
    select 2 id, 'abc def iosk www' str from dual
), num_gen as (
    -- a number generator up to the minimum length of the strings
    SELECT level num
    FROM dual t
    CONNECT BY level <= (select min(length(str)) from cte1)
), cte2 as (
    -- build substrings of increasing length
    select id, num_gen.num, substr(cte1.str, 1, num_gen.num) sub
    from cte1
    cross join num_gen
), cte3 as (
    -- self join to check if the substrings are equal
    select x1.num, x1.sub sub1, x2.sub sub2
    from cte2 x1
    join cte2 x2 on (x1.num = x2.num and x1.id != x2.id)
), cte4 as (
    -- select maximum string length
    select max(num) max_num
    from cte3
    where sub1 = sub2
)
    -- finally, get the substring with the max length
    select cte3.sub1
    from cte3
    join cte4 on (cte4.max_num = cte3.num)
    where rownum = 1

本质上，这就是您在 pl/sql 中要做的:构建长度增加的子字符串并在它们不再匹配的点处停止.

Essentially, this is what you would do in pl/sql: Build substrings of increasing length and stop at the point at which they are not matching anymore.

这篇关于Oracle函数返回字符串之间的相似性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！