CODEWITHSUNDEEP
sql-serversubstringudfpatindex
user3486773
user3486773

Reputation: 1246

Search SQL Server string for values from another table

I have a table with column name that has random characters before and after the name ie:

Table A:

  Name
  -----------------
  asd4345JONlkj345
  .;lidDavidlksd$

and I have another table in same DB that has the names ie:

Table 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.

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.

Upvotes: 0

Views: 5014

Answers (2)

Esperento57
Esperento57

Reputation: 17472

  select B.Name, A.Name from tableA A inner join join tableB B
  on rtrim(ltrim(B.Name)) like '%' + rtrim(ltrim(A.Name)) + '%'

Upvotes: 1

SlimsGhost
SlimsGhost

Reputation: 2909

You only need the LIKE operator for this:

select * 
from TableA
inner join TableB on TableA.Name like '%' + TableB.Name + '%'

Upvotes: 3

Related Questions