Not sure how to write a query to join on a partial string match. See screen shot for example. Table1 has a list of prefixes I need to find within the Caption of Table2. The Prefix differs in length, but is unique. For each match in Table2, I need to include some other info from Table1 in my output. If the LEN of Prefix was fixed, I'd use the LEFT x characters of Caption, but that's not the case. Any thoughts appreciated.
on the JOIN condition
Table2.Caption LIKE Table1.Prefix + '%'
Hi here is an example for you it may be helpful
> dbo.TableA.Name
> INNER JOIN dbo.TableB.Caption
> ON dbo.TableA.Name LIKE '%' + dbo.TableB.Caption + '%'
> OR dbo.TableB.Caption LIKE '%' + dbo.TableA.Name + '%'
Wow, thank you both! That works great. I feel a little silly for such a simple solution, but I've never had cause to do this before. MUCH appreciated.
Beware that performance is not good, and it would be "better" if database design of Table2 had the Prefix in a separate column to the Suffix, as the performance of a straight equals join would be better.
I'm only raising this just so you are aware (if you weren't already 
), back in the real world we have to do what we have to do ...
So you want multiple matches from Table1 and not just the single longest/best match?
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.