本文介绍了SQL Server:从结果集中删除子字符串结果的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
请帮忙查询
请帮助进行 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:从结果集中删除子字符串结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！