SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE MainTbl(UNDERWRITER_CODE int,  UNDERWRITER_NAME varchar(100))
INSERT INTO MainTbl VALUES
(NULL,'dylan.campbell'),
(NULL,'dylanadmin'),
(NULL,'dylanc'),
(002,'Dylan Campbell'),
(002,'dylan.campbell'),
(002,'dylanadmin'),
(NULL,'scott.noffsinger'),
(001,'Scott Noffsinger');


CREATE TABLE LKP(UNDERWRITER_CODE int,  UNDERWRITER_NAME varchar(100))
INSERT INTO LKP VALUES
(002,'Dylan Campbell'),
(001,'Scott Noffsinger');
10 rows affected

SELECT * FROM MainTbl


UNDERWRITER_CODE
UNDERWRITER_NAME


null
dylan.campbell

null
dylanadmin

null
dylanc

2
Dylan Campbell

2
dylan.campbell

2
dylanadmin

null
scott.noffsinger

1
Scott Noffsinger


SELECT *, CONCAT('%', LEFT( LOWER(T2.UNDERWRITER_NAME)
                                             ,CHARINDEX(' '
                                                        ,LOWER(T2.UNDERWRITER_NAME)
                                                       ) - 1
                                            )
                                      , '%'
                                   ) Join_Col
FROM LKP T2


UNDERWRITER_CODE
UNDERWRITER_NAME
Join_Col


2
Dylan Campbell
%dylan%

1
Scott Noffsinger
%scott%


UPDATE T1 SET T1.UNDERWRITER_CODE = T2.UNDERWRITER_CODE
FROM MainTbl T1 
INNER JOIN LKP T2
ON T1.UNDERWRITER_NAME LIKE CONCAT('%', LEFT( LOWER(T2.UNDERWRITER_NAME)
                                             ,CHARINDEX(' '
                                                        ,LOWER(T2.UNDERWRITER_NAME)
                                                       ) - 1
                                            )
                                      , '%'
                                   )
8 rows affected

SELECT * FROM MainTbl


UNDERWRITER_CODE
UNDERWRITER_NAME


2
dylan.campbell

2
dylanadmin

2
dylanc

2
Dylan Campbell

2
dylan.campbell

2
dylanadmin

1
scott.noffsinger

1
Scott Noffsinger

UNDERWRITER_CODE	UNDERWRITER_NAME
null	dylan.campbell
null	dylanadmin
null	dylanc
2	Dylan Campbell
2	dylan.campbell
2	dylanadmin
null	scott.noffsinger
1	Scott Noffsinger