SQLTeam.com | Weblogs | Forums

How to join on partial string match


#1

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.


#2

on the JOIN condition

Table2.Caption LIKE Table1.Prefix + '%'


#3

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 + '%'


#4

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.


#5

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 :slightly_smiling: ), back in the real world we have to do what we have to do ...


#6

So you want multiple matches from Table1 and not just the single longest/best match?


#7

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.


#8

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.