I have a table with column name that has random characters before and after the name ie:
我有一个列名称的表，在名称前后有随机字符，即：
Table A:
表A：
Name
-----------------
asd4345JONlkj345
.;lidDavidlksd$
and I have another table in same DB that has the names ie:
我在同一个数据库中有另一个表，其名称为：
Table B:
表B：
Name
------
David
Jon
This goes on like this for 30k rows or I'd just hardcode something really quick. I want to search each string in Table A 'Name' column for each value from Table B, and if found return the name in a new column.
对于30k行，这种情况就像这样，或者我只是快速硬编码。我想在表A'Name'列中搜索表B中每个值的每个字符串，如果找到则返回新列中的名称。
I have a feeling this will be a UDF, which is fine, I'm just unsure how to use patindex in this scenario, or if that is even the right approach.
我觉得这将是一个UDF，这很好，我只是不确定如何在这种情况下使用patindex，或者如果这是正确的方法。
2 个解决方案
#1
3
You only need the LIKE operator for this:
您只需要LIKE运算符：
select *
from TableA
inner join TableB on TableA.Name like '%' + TableB.Name + '%'
#2
1
select B.Name, A.Name from tableA A inner join join tableB B
on rtrim(ltrim(B.Name)) like '%' + rtrim(ltrim(A.Name)) + '%'
#1
3
You only need the LIKE operator for this:
您只需要LIKE运算符：
select *
from TableA
inner join TableB on TableA.Name like '%' + TableB.Name + '%'
#2
1
select B.Name, A.Name from tableA A inner join join tableB B
on rtrim(ltrim(B.Name)) like '%' + rtrim(ltrim(A.Name)) + '%'