Join where field is like a field

Transact-SQL
#1

I'm doing a inner join of 2 tables. Field unitpo in table a is a text field with multiple unit numbers separated by commas such as "12345, 56789, 91234, 45678" Field unit in table b is just 12345.

Select a., b.
from a
join b on b.unit like a.unitpo

does not work.

Is there a way to do this?

#2

Split the combined field, then join on the split results. You can Google "dbo.DelimitedSplit8K" to get the code for that function.


SELECT a.column, ds.unit, b.joined_column
FROM a
CROSS APPLY dbo.DelimitedSplit8K ( a.unitpo, ',') AS ds
INNER JOIN b ON b.unit = ds.unit
1 Like
#3

Thank you Scott. Worked perfect.