This should get you started:

-- Create some sample data to play with

DROP TABLE #Fullname

CREATE TABLE #Fullname (ID int identity(1,1), Fullname VARCHAR(50))

INSERT INTO #Fullname (fullname)

SELECT 'Phill R Williams' UNION ALL

SELECT 'P Smith' UNION ALL

SELECT 'William Johns' UNION ALL

SELECT 'Sam Brown'

DROP TABLE #Splitname

CREATE TABLE #Splitname (ID int identity(1,1), Firstname VARCHAR(50), Lastname VARCHAR(50))

INSERT INTO #Splitname (Firstname, Lastname)

SELECT 'Phil', 'Williams' UNION ALL

SELECT 'Peter', 'Smith' UNION ALL

SELECT 'Will', 'Johns' UNION ALL

SELECT 'Sam', 'Browne'

SELECT

r.*,

t.Fullname,

m2.*,

[Match%] = CAST((m2.FirstnameMatchPC + m2.LastnameMatchPC)/2.00 AS DECIMAL(5,2))

FROM #Splitname r

INNER JOIN #Fullname t ON t.ID = r.ID

-- find the position of the rightmost space in the surname

-- this is assumed to be the space between forename(s) and surname

-- use it to split the fullname string into firstname and lastname

CROSS APPLY(

SELECT

Firstname = LEFT(fullname,Split.Position-1),

Lastname = SUBSTRING(fullname,Split.Position+1,(DATALENGTH(fullname)-Split.Position+1))

FROM (

SELECT

Position = MAX(n)

FROM ( -- create sequential list of numbers where largest = length of t.fullname

SELECT n = t1.n+t2.n

FROM (VALUES (0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) t1(n)

CROSS JOIN (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) t2(n)

WHERE t1.n+t2.n <= (ISNULL(DATALENGTH(t.fullname),0))

) tally

WHERE SUBSTRING(t.fullname,n,1) = ' '

) Split

) t2

-- attempt to match at the simplest level

CROSS APPLY (

SELECT

FirstnameMatchPC = CASE

WHEN t2.Firstname = r.Firstname THEN 100

WHEN (len(t2.Firstname) = 1 OR len(r.Firstname) = 1) AND LEFT(t2.Firstname,1) = LEFT(r.Firstname,1) THEN 20

WHEN (t2.Firstname LIKE r.Firstname + '%' OR r.Firstname LIKE t2.Firstname + '%') THEN 50

ELSE 0 END,

LastnameMatchPC = CASE WHEN t2.Lastname = r.Lastname THEN 100 ELSE 0 END

) m1

-- when simple matching fails, get the hammer out

CROSS APPLY (

SELECT

FirstnameMatchPC = CASE

WHEN m1.FirstnameMatchPC > 0 THEN m1.FirstnameMatchPC -- carry forward the result from previous step / only use token matching where necessary

ELSE (

SELECT MatchPC = CAST(100*(COUNT(*) / (LEN(r.Firstname)-2.00)) AS DECIMAL(5,2))

FROM (

SELECT n = t1.n+t2.n

FROM (VALUES (0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) t1(n)

CROSS JOIN (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) t2(n)

WHERE t1.n+t2.n <= (ISNULL(DATALENGTH(r.Firstname),0))

) Tally

WHERE CHARINDEX(SUBSTRING(r.Firstname, Tally.n, 3), t2.Firstname) > 0

AND LEN(SUBSTRING(r.Firstname, Tally.n, 3)) = 3

)

END,

LastnameMatchPC = CASE

WHEN m1.LastnameMatchPC > 0 THEN m1.LastnameMatchPC

ELSE (

SELECT MatchPC = CAST(100*(COUNT(*) / (LEN(r.Lastname)-2.00)) AS DECIMAL(5,2))

FROM (

SELECT n = t1.n+t2.n

FROM (VALUES (0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) t1(n)

CROSS JOIN (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) t2(n)

WHERE t1.n+t2.n <= (ISNULL(DATALENGTH(r.Lastname),0))

) Tally

WHERE CHARINDEX(SUBSTRING(r.Lastname, Tally.n, 3), t2.Lastname) > 0

AND LEN(SUBSTRING(r.Lastname, Tally.n, 3)) = 3

)

END

) m2