Join where field is like a field

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?

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
CROSS APPLY dbo.DelimitedSplit8K ( a.unitpo, ',') AS ds
INNER JOIN b ON b.unit = ds.unit
1 Like

Thank you Scott. Worked perfect.


hope this helps

you had it right ... but forgot '%'

Nope, that doesn't work correctly if unitpos can vary in length (which we would assume they could).

I can't copy and run your code but try #a = '123456, ...' and run your code -- I think you will still get a match, but really shouldn't.

hi Scott

A solution for that would be

create data script

drop table #a
create table #a (unitpos varchar(100))
insert into #a select '123456,56789,9124'

drop table #b
create table #b (unit varchar(10))
insert into #b select '12345'

select * from #a a join #b b on a.unitpos like '%'+b.unit+','+'%'


Close, but not quite there yet.

hi Scott

this hopefully is the final solution but all depends on the variations in data

select * from #a a join #b b on ','+a.unitpos+',' like '%'+','+b.unit+','+'%'









Yep, that should do it, although very inefficiently because of the repeated pattern matching.

hi Scott

Sometimes ineffiecient ways can lead to better performance and execution plans

i have not checked on test data .. small , medium , huge
with execution plan comparision
and performance testing

My only aim was to .. do it in the way the OP posted

As far as variations in data .. there are multiple data scenarios where your delimited would not work
all depends on data scenarios

I could be wrong ..but just my two cents