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
FROM a
CROSS APPLY dbo.DelimitedSplit8K ( a.unitpo, ',') AS ds
INNER JOIN b ON b.unit = ds.unit
Thank you Scott. Worked perfect.
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
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
