SQL Server:从结果集中删除子字符串结果 [英] SQL Server : remove substring results from result set

问题描述

请帮忙查询

请帮助进行 T-SQL 查询

Please help with the T-SQL query

假设表中有如下数据

| ID  | Name | FullName |
|  1  | a    | a        |
|  2  | b    | ab       |
|  3  | c    | abc      |
|  4  | d    | ad       |
|  5  | e    | ade      |
|  6  | i    | i        |
|  7  | g    | ig       |

我想得到如下的结果集

| ID | Name | FullName |
| 3  | c    | abc      | 
| 5  | e    | ade      |
| 7  | g    | ig       |

推荐答案

要检查子字符串，您可以使用内置的 CHARINDEX 函数.子查询查找与任何其他行具有子字符串匹配的任何行.然后从最终结果集中过滤这些 ID.

To check for a substring you can use the builtin CHARINDEX function. The subquery looks for any rows that have a substring match to any other row. Those ids are then filtered from the final result set.

create table #example (
    Id int, [Name] varchar(255), [FullName] varchar(255)
);
go

insert into #example (Id, Name, FullName)
values
(1, 'a', 'a'),
(2, 'b', 'ab'),
(3, 'c', 'abc'),
(4, 'd', 'ad'),
(5, 'e', 'ade'),
(6, 'i', 'i'),
(7, 'g', 'ig');
go



select *
from #example as a where a.Id not in (
    select distinct
        a.Id
    from
        #example as a
        inner join #example as b
        on a.Id <> b.Id -- don't check against yourself
        and charindex(a.FullName, b.FullName, 0) > 0 -- if charindex > 0 then there is a substring match
)

这篇关于SQL Server:从结果集中删除子字符串结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！