Table 1 below:

City
Jiangsu
Hubei
Shanghai
Shanxi
Nantong
Wuhan


Table 2 is as follows:

Keyword
Shanghai it training
Dark horse hui Shanghai education college
It is the best school school what school
Jiangsu it education
Where to learn good performance
Hubei wuhan Hepburn makeup nail art training school introduced

to get the following results (according to table 1 word batch matches the data in table 2)
Keyword city
Shanghai it training
Dark horse hui Shanghai education college
It is the best school school what school is NULL
It education in jiangsu jiangsu
Where to learn good performance NULL
Hubei wuhan Hepburn makeup nail art training school in wuhan

CodePudding user response:

 declare @ tb1 table (city nvarchar (20)) 
Declare @ tb2 table (keyword nvarchar (200)) 

Insert into @ tb1 values (' jiangsu), (' hubei), (' Shanghai '), (' shanxi), (' nantong), (' wuhan) 
Insert into @ tb2 values (' Shanghai it training), (' dark horse hui Shanghai institute of education), (' what is the best school is it school), (' the it education in jiangsu province), (' where learning performance is good), (' hubei wuhan Hepburn makeup nail art training school to introduce ') 

Select * from @ tb2 a 
The outer apply (
Select (
Select "+ city 
The from @ tb1 
Where charindex (city, keyword) & gt; 0 
The order by charindex (city, keyword) 
For XML path (' ') 
) as city 
B) 

CodePudding user response:

DECLARE @ tb1 TABLE (city NVARCHAR (20));
DECLARE @ tb2 TABLE (keyword NVARCHAR (200));
INSERT INTO @ tb1 VALUES (' jiangsu), (' hubei), (' Shanghai '), (' shanxi), (' nantong), (' wuhan ');
INSERT INTO @ tb2
VALUES (' Shanghai it training),
(' dark horse hui Shanghai institute of education),
(' 'what is the best school is it school),
(' it education in jiangsu province),
(' where learn good performance),
(' hubei wuhan Hepburn makeup nail art training school to introduce ');
The SELECT a.k eyword, (SELECT TOP 1 a1. City FROM @ tb1 a1 WHERE PATINDEX (' % '+ a1. City +' % ', a.k eyword) & gt; 0) city
The FROM @ tb2 a;