SQL Server 2008 R2:模式匹配字符串反之亦然 [英] SQL Server 2008 R2: Pattern matching string vice versa
问题描述
我有下表:
表格:
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')
INSERT INTO str_matching VALUES('9001')
INSERT INTO str_matching VALUES('9001A')
预期输出:我只想显示那些有重复条目的记录,如果一个字符串与任何字符串的最后一部分匹配,那么我认为是重复的.
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
A1010B
与第一个字符串末尾匹配的第二个字符串,因此要显示此类记录.
2nd string which is matching at end of 1st string, so want to display such records.
场景:2
例如:我有两个字符串
9001
9001A
第一个字符串与第二个字符串的第一个匹配,所以想显示这样的记录.
1st string which is matching at first of 2nd 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
9001
9001A
注意:需要检查反之亦然模式匹配.
Note: Need to check the vice versa pattern matching.
根据 Martin Smith 代码,我已修改为:>
As per Martin Smith code, I have modified to:
SELECT DISTINCT CA.colstr
FROM str_matching s1
JOIN str_matching s2
ON s1.colstr <> s2.colstr
AND s2.colstr LIKE '%' + s1.colstr
OR s1.colstr LIKE '%' + s2.colstr
CROSS APPLY (VALUES(s1.colstr),
(s2.colstr)) CA(colstr)
但无法获得给定的字符串集.
But unable to get the given set of strings.
推荐答案
这对我有用:
SELECT DISTINCT CA.colstr
FROM str_matching s1 JOIN
str_matching s2
ON s1.colstr <> s2.colstr AND
(s1.colstr LIKE s2.colstr + '%' OR
s2.colstr LIKE '%'+ s1.colstr
) CROSS APPLY
(VALUES(s1.colstr), (s2.colstr)) as CA(colstr) ;
这篇关于SQL Server 2008 R2:模式匹配字符串反之亦然的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!