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 |