匹配任意4个连续字符的子字符串

Question

我正在寻找匹配来自两个不同表的字符串的连接表达式，这两个表都包含4个连续字符的相同子字符串。

例如，下面应符合：

String1 String2 
-------- ----------- 
xxjohnyy abcjohnabc  [common substring: "john"] 
xxjohnyy johnny   [common substring: "john"] 
birdsings ravenbird  [common substring: "bird"] 
singbird a singer  [common substring: "sing"] 

Answer 1

这个问题很相似，找到Longest Common Substring问题。你找到最长的公共子串，然后你选择共同字符串为4的那些。你一定会发现this link和this link对你很有帮助。

Answer 2

这是一个很好的练习。这是我使用Tally Table的尝试。

SQL Fiddle

;WITH E1(N) AS(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
), 
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b), 
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), 
E8(N) AS(SELECT 1 FROM E4 a CROSS JOIN E4 b), 
Tally(N) AS(
    SELECT TOP (
     SELECT 
      CASE 
       WHEN MAX(LEN(String1)) > MAX(LEN(String2)) THEN MAX(LEN(String1)) 
       ELSE MAX(LEN(String2)) 
      END 
     FROM TestTable 
    ) 
     ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) 
    FROM E8 
), 
CteTable AS(-- Added an ID to uniquely identify each row 
    SELECT *, Id = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM TestTable 
), 
CteSubStr1 AS(
    SELECT 
     ct.*, 
     substr = SUBSTRING(ct.String1, t.N, 4) 
    FROM CteTable ct 
    CROSS APPLY(
     SELECT N FROM Tally 
     WHERE N <= LEN(ct.String1) - 3 
    )t 
), 
CteSubStr2 AS(
    SELECT 
     ct.*, 
     substr = SUBSTRING(ct.String2, t.N, 4) 
    FROM CteTable ct 
    CROSS APPLY(
     SELECT N FROM Tally 
     WHERE N <= LEN(ct.String2) - 3 
    )t 
), 
CteCommon AS(
    SELECT * FROM CteSubStr1 c1 
    WHERE EXISTS(
     SELECT 1 FROM CteSubStr2 
     WHERE 
      Id = c1.Id 
      AND substr = c1.substr 
    ) 
) 
SELECT 
    String1, String2, substr 
FROM (
    SELECT *, RN = ROW_NUMBER() OVER(PARTITION BY Id ORDER BY LEN(substr) DESC) 
    FROM CteCommon 
)t 
WHERE RN = 1 

结果

| String1 | String2 | substr | 
|-----------|------------|--------| 
| xxjohnyy | abcjohnabc | john | 
| xxjohnyy |  johnny | john | 
| birdsings | ravenbird | bird | 
| singbird | a singer | sing | 

---

这部分寻找最长公共子。

SELECT 
    String1, String2, substr 
FROM (
    SELECT *, RN = ROW_NUMBER() OVER(PARTITION BY Id ORDER BY LEN(substr) DESC) 
    FROM CteCommon 
)t 
WHERE RN = 1 

要获得所有常见字符串，而不是使用：

SELECT * FROM CteCommon 

Answer 3

;with pos as(select 1 as p 
      union all 
      select p + 1 from pos where p < 100), 
     uni as(select *, row_number() over(order by (select null)) id from t) 
select t1.s1, t1.s2, ca.s 
from uni t1 
cross apply(select substring(t2.s2, p, 4) s 
      from uni t2 
      cross join pos 
      where t1.id = t2.id and 
        len(substring(t2.s2, p, 4)) = 4 and 
        t1.s1 like '%' + substring(t2.s2, p, 4) + '%')ca 

Fiddlee http://sqlfiddle.com/#!3/bd4dd/16

只要改变100你列的实际长度......