Sql String search

Question

Hi,

I have the below result set from query 1. Now I need to count the key when the names are totally different for the same key (as highlighted below) if they are partially same (for ex siddharth and Sid) then we don’t need to count them.

Answer 1

Hi @Rajapriya Rajendran ,

Welcome to Microsoft Q&A!

Please refer below example and check whether it is working.

drop table if exists Search  
  
create table Search  
(name varchar(100),  
[Thekey] int)  
  
insert into Search values  
('Blum,Kaye',294426789),  
('BLUM,KAYE ELIZABETH',294426789),  
('BAKER,MICHELLE',294739049),  
('BOOT,DAMEN',294739049)  
  
select count(distinct a.[Thekey]) count  
from Search a  
left join Search b   
on a.[Thekey]=b.[Thekey]   
where a.name not like '%'+b.name+'%'   
and b.name not like '%'+a.name+'%'   

OR

 select count(distinct a.[Thekey]) count  
 from Search a  
 left join Search b   
 on a.[Thekey]=b.[Thekey]   
 where CHARINDEX(a.name,b.name)=0   
 and CHARINDEX(b.name,a.name)=0  

If above is not working, please provide more sample data, details of rule and expected output.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.