SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE TEMP1 (NAME NVARCHAR(300))
CREATE TABLE TEMP2 (NAME NVARCHAR(300))

INSERT TEMP1 SELECT 'Paul Smith'
INSERT TEMP1 SELECT 'Amy Nicholas Stanton'
INSERT TEMP1 SELECT 'Andrew James Thomas'

INSERT TEMP2 SELECT 'Paul Andrew Smith'
INSERT TEMP2 SELECT 'Amy Stanton'
INSERT TEMP2 SELECT 'Andrew Marcus Thomas'
6 rows affected

      select temp1.*, count(value) over (partition by name) as num_parts
      from temp1 cross apply
           string_split(temp1.name, ' ') s(part)
Msg 195 Level 15 State 15 Line 3
'string_split' is not a recognized function name.

with n1 as (
      select temp1.name, value as part, count(value) over (partition by name) as num_parts
      from temp1 cross apply
           string_split(temp1.name, ' ')
     ),
     n2 as (
      select temp2.name, value as part, count(value) over (partition by name) as num_parts
      from temp2 cross apply
           string_split(temp2.name, ' ') 
     )
select n1.name, n2.name
from n1 join
     n2
     on n1.part = n2.part and n1.num_parts <= n2.num_parts
group by n1.name, n2.name, n1.num_parts
having count(*) = n1.num_parts



name
name


Paul Smith
Paul Andrew Smith