How to join on partial string match

[JeffModen]

@Reid

No, I want all rows from Table2 where the Caption contains Table1.Prefix. For each match, I will include other info such as Table1.BuildingName. The solutions above worked perfect for me.

If you use the one with the leading %'s, then you're in for a bit of a shock when the table gets bigger because such queries are not SARGable.