SQL Server 2008 R2:匹配字符串 [英] SQL Server 2008 R2: Matching strings

问题描述

我有下表:

表格:

CREATE TABLE str_matching
(
    colstr varchar(200)
);

插入数据:

INSERT INTO str_matching VALUES('5sXYZA1010B')
INSERT INTO str_matching VALUES('A1010B')
INSERT INTO str_matching VALUES('AMZ103B15K')
INSERT INTO str_matching VALUES('B15K')
INSERT INTO str_matching VALUES('XC101')
INSERT INTO str_matching VALUES('C101')
INSERT INTO str_matching VALUES('502KMD1FZ10009L')
INSERT INTO str_matching VALUES('FZ10009L')
INSERT INTO str_matching VALUES('A9L')
INSERT INTO str_matching VALUES('XZ049L')
INSERT INTO str_matching VALUES('LM101')

预期输出:我只想显示那些有重复条目的记录，如果一个字符串与任何字符串的最后一部分匹配，那么我认为是重复的.

Expected Output: I want to display only those records that has duplicate entries, if one string match last part of any string then I am considering as duplicate.

例如:我有两个字符串

1. 5sXYZA1010B
2. A1010B

与第一个字符串末尾匹配的第二个字符串，因此要显示此类记录.

2nd string which is matching at end of 1st string, so want to display such records.

注意:字符串的长度不固定，可以任意匹配.

Note: Length of string's are not fixed, it can be match at any point.

预期结果:

colstr              
--------------------
5sXYZA1010B         
A1010B              
AMZ103B15K          
B15K                
XC101               
C101                
502KMD1FZ10009L     
FZ10009L        

推荐答案

应该这样做 (demo)

SELECT DISTINCT CA.colstr
FROM   str_matching s1
       JOIN str_matching s2
         ON s1.colstr <> s2.colstr
            AND s2.colstr LIKE '%' + s1.colstr
       CROSS APPLY (VALUES(s1.colstr),
                          (s2.colstr)) CA(colstr) 

但是如果 str_matching 有很多行，性能会很差.在字符串的背面添加索引可以显着改善情况 - 如下例所示.

However if str_matching has many rows performance will be poor. Adding an index on the reverse of the string can substantially improve things - as in the example below.

CREATE TABLE str_matching
(
    colstr varchar(200),
    colstr_rev AS REVERSE(colstr)
);

CREATE INDEX ix_colstr_rev on str_matching(colstr_rev)

SELECT colstr = REVERSE(CA.colstr_rev)
FROM   str_matching s1
       JOIN str_matching s2
         ON s1.colstr_rev <> s2.colstr_rev
            AND s2.colstr_rev LIKE  s1.colstr_rev + '%' 
       CROSS APPLY (VALUES(s1.colstr_rev),
                          (s2.colstr_rev)) CA(colstr_rev) 
GROUP BY CA.colstr_rev

这篇关于SQL Server 2008 R2:匹配字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！