String search with any sequence

Question

I have the following two tables:

Table 1:

CREATE TABLE tbl_str_match_1
(
    enumber int,
    ename varchar(100),
    eaddress varchar(500)
);

INSERT INTO tbl_str_match_1 VALUES(1,'John Mak','Hno 12 Street Road, USA');
INSERT INTO tbl_str_match_1 VALUES(2,'Shai Lee','UK');
INSERT INTO tbl_str_match_1 VALUES(3,'Smith Watson','Street X01 UAE');
INSERT INTO tbl_str_match_1 VALUES(4,'Ray Gibbs','SA 124');

Table 2:

CREATE TABLE tbl_str_match_4
(
    name varchar(100),
    [address] varchar(500)
);

INSERT INTO tbl_str_match_4 VALUES('Mak John','Street Road, Hno 12, USA');
INSERT INTO tbl_str_match_4 VALUES('Shai A Lee','UK');
INSERT INTO tbl_str_match_4 VALUES('A watson Smeeth ','UAE Street X01');
INSERT INTO tbl_str_match_1 VALUES('Henry Jay','RUS OP124');

I want to search name from table tbl_str_match_1 with the passed number and follow the next search with name as input and find the name and address from another table called as tbl_str_match_4.

Note:

1. Names may be in any sequence like first mid last name or mid last first name or last first mid name, any probability is possible.

2. I want to find the name and address from second table with one extra column i.e percentage match of the string.

3. There will be two search, first on table tbl_str_match_1 to get name and second on table tbl_str_match_4 to get name and address.

4. For the first record John Mak it should show 100% match with Mak John.

5. For the second record Shai Lee should show 90% match with Shai A Lee because of A mid name appearance.

6. Last record that is Ray Gibbs will not show in the result set as it has no match with other table values.

--Query:

WITH CTE1 AS
(   
    SELECT ename FROM tbl_str_match_1 WHERE enumber = 1
)
SELECT name,[address] FROM tbl_str_match_4 WHERE name LIKE '%'+(SELECT ename from CTE1)+'%'

Expected Result:

Scenario 1: If I pass enumber = 1 then result should be:

    Name        Address                     Matching Percentage
    ------------------------------------------------------------
    Mak John    Street Road, Hno 12, USA    100

Scenario 2: If I pass enumber = 2 then result should be:

    Name        Address                     Matching Percentage
    ------------------------------------------------------------
    Shai A Lee  UK                          90

Scenario 3: If I pass enumber = 3 then result should be:

    Name                Address             Matching Percentage
    ------------------------------------------------------------
    A watson Smeeth     UAE Street X01      70

Scenario 4: If I pass enumber = 4 then result should be:

NO RESULT for this, because we don't any relavent match.

    Name        Address                     Matching Percentage
    ------------------------------------------------------------

Answer 1

You can make use of CTE in combination with STRING SPLIT to do the work

I have added an Identity column in tbl_str_match_4 to make this simpler

 DECLARE @enumber INT = 2

;WITH c1 AS 
( 
  --To split the ename from first  table 

   SELECT s.value AS name
   FROM tbl_str_match_1 t
   CROSS APPLY STRING_SPLIT(t.ename, ' ') AS s
   WHERE enumber=@enumber
)
,c2 AS
( 
   --To split the matching names from second table of matched records

   SELECT t.id,s.value AS name 
   FROM tbl_str_match_4 t
   CROSS APPLY STRING_SPLIT(t.name, ' ') AS s
   WHERE EXISTS(SELECT 1 FROM c1 c WHERE t.name LIKE '%'+c.name+'%')
)
,c3 AS 
( 
   --To calculate the percentage of match

   SELECT id,
   CAST (COUNT(c1.name) AS FLOAT )/ CAST (COUNT(c2.name) AS FLOAT ) * 100 As Percentage
   FROM c2
   LEFT JOIN  c1 on c1.name =c2.name
   GROUP BY id
) 
--display the details
SELECT t.*,c3.Percentage FROM tbl_str_match_4 t
JOIN c3 ON t.Id=c3.Id

FOR DEMO

Answer 2

Hope the following helps.

I first tokenize the names in tbl_1 and tbl_4 names by

After that i compare the tokens in tbl_1 with tbl_4

A question on the matching percentage. In the example of "Shai A Lee" you have 2 matches("Shai","Lee") out of total of 3("Shai","A","Lee") so shouldnt the matching percentage be 66.67 ?

with split_ename_1 
  as (
        SELECT a.enumber
            ,a.ename
            ,a.eaddress      
            ,split.a.value('.', 'VARCHAR(100)') AS Data  
        FROM  
        (
            SELECT enumber
                ,ename
                ,eaddress
                ,CAST ('<M>' + REPLACE(rtrim(ename), ' ', '</M><M>') + '</M>' AS XML) AS Data  
            FROM  tbl_str_match_1
        ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a)
     )
,split_ename_4
   as (SELECT a.name            
             ,a.address      
             ,split.a.value('.', 'VARCHAR(100)') AS Data  
             ,COUNT(*) over(partition by a.name) as  tot_cnt
        FROM  
        (
            SELECT name
                   ,address
                   ,CAST ('<M>' + REPLACE(rtrim(name), ' ', '</M><M>') + '</M>' AS XML) AS Data  
              FROM  tbl_str_match_4
        ) AS A CROSS APPLY data.nodes ('/M') AS split(a)
       )
   select a.ename
         ,count(a.data) as tokens_1
         ,count(b.data) as tokens_4
         ,max(b.tot_cnt) as tot_tokens_4
         ,case when count(b.data)=0 then 0 else count(b.data)*1.00/max(b.tot_cnt)*1.00 end as matching_percentage
     from split_ename_1 a
left join split_ename_4 b
       on a.data=b.data
group by a.ename

Answer 3

I hope this may help you.

with CTE1 as

(
Select enumber,Ltrim(SubString(ename,1,Isnull(Nullif(CHARINDEX(' ',ename),0),1000))) As Firstename,

Ltrim(SUBSTRING(ename,CharIndex(' ',ename),
CAse When (CHARINDEX(' ',ename,CHARINDEX(' ',ename)+1)-CHARINDEX(' ',ename))<=0 then 0 
else CHARINDEX(' ',ename,CHARINDEX(' ',ename)+1)-CHARINDEX(' ',ename) end )) as Middleename,

Ltrim(SUBSTRING(ename,Isnull(Nullif(CHARINDEX(' ',ename,Charindex(' ',ename)+1),0),CHARINDEX(' ',ename)),
Case when Charindex(' ',ename)=0 then 0 else LEN(ename) end)) as Lastename

From tbl_str_match_1
),

CTE2 as

(
Select *,Ltrim(SubString(name,1,Isnull(Nullif(CHARINDEX(' ',name),0),1000))) As FirstName,

Ltrim(SUBSTRING(name,CharIndex(' ',name),
CAse When (CHARINDEX(' ',name,CHARINDEX(' ',name)+1)-CHARINDEX(' ',name))<=0 then 0 
else CHARINDEX(' ',name,CHARINDEX(' ',name)+1)-CHARINDEX(' ',name) end )) as MiddleName,

Ltrim(SUBSTRING(name,Isnull(Nullif(CHARINDEX(' ',name,Charindex(' ',name)+1),0),CHARINDEX(' ',name)),
Case when Charindex(' ',name)=0 then 0 else LEN(name) end)) as LastName

From tbl_str_match_4
)

select CTE2.name,CTE2.address from CTE1 inner join CTE2 on  CTE1.Firstename = CTE2.FirstName and CTE1.Lastename = CTE2.LastName
where CTE1.enumber = 1