SAS/SQL join based on one column pattern matches another column

Question

I have 2 datasets that I need to match on a id field:

data1:

id
---------
00123abc5

data2:

id
---
23a

I need to merge (SQL style inner join) these two datasets on id column but not data1.id = data2.id. What I want is data1.id contains data2.id.

So I want all rows where the id string in the first dataset contains the id string of the second dataset.

Answer 1

In SQL, use the LIKE operator :

SELECT d1.id
FROM data1 d1 
INNER JOIN data2 d2 ON d1.id LIKE CONCAT('%', d2.id, '%')

---

As commented by TheMouseMaster, this technique might generate duplicated output rows if several ids happen to be found in the same field. You can use DISTINCT to eliminate the duplicates :

SELECT DISTINCT d1.id
FROM data1 d1 
INNER JOIN data2 d2 ON d1.id LIKE CONCAT('%', d2.id, '%')