SQL Server 2017

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

select @@version;


(No column name)


Microsoft SQL Server 2017 (RTM-CU12) (KB4464082) - 14.0.3045.24 (X64) 
	Oct 18 2018 23:11:05 
	Copyright (C) 2017 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)



create table table1
 ( title varchar(100) not null
 ) ;
insert into table1 (title)
values
('Jack'),
('Benz'),
('Volvoo') ;
3 rows affected

create table table2
 ( description varchar(100) not null,
   names varchar(100) null
 ) ;
insert into table2 (description)
values
('The Jack   isnt A122'),
('Some  B1234 Benz'),
('This Volvoo Are C'),
('This Maria Are D'),
('Michael Jackson'),
('Jack owns a Benz'),
('Mercedes-Benz Yeah why') ;
7 rows affected

select s.description, coalesce(t.title, 'Others') as title                   -- show matches
from table2 as s
  left join table1 as t
  on concat(' ', s.description, ' ') LIKE  concat('% ', t.title, ' %') ;


description
title


The Jack   isnt A122
Jack

Some  B1234 Benz
Benz

This Volvoo Are C
Volvoo

This Maria Are D
Others

Michael Jackson
Others

Jack owns a Benz
Jack

Jack owns a Benz
Benz

Mercedes-Benz Yeah why
Others


update s                                         -- do the update
set s.names = coalesce(t.title, 'Others') 
from table2 as s
  left join table1 as t
  on concat(' ', s.description, ' ') LIKE  concat('% ', t.title, ' %') ;
7 rows affected

select * from table2 ;


description
names


The Jack   isnt A122
Jack

Some  B1234 Benz
Benz

This Volvoo Are C
Volvoo

This Maria Are D
Others

Michael Jackson
Others

Jack owns a Benz
Jack

Mercedes-Benz Yeah why
Others

description	title
The Jack isnt A122	Jack
Some B1234 Benz	Benz
This Volvoo Are C	Volvoo
This Maria Are D	Others
Michael Jackson	Others
Jack owns a Benz	Jack
Jack owns a Benz	Benz
Mercedes-Benz Yeah why	Others