SQL Server : remove substring results from result

问题:

Please help with the query

Please help with the T-SQL query

Let's assume the table has the data like below

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

I want to get the result set as below

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

回答1:

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
)