JOIN with LIKE?

Question

Post reply

JOIN with LIKE?

rschaeferhig

SSCrazy Eights

Points: 8776
More actions
July 19, 2006 at 10:54 am

#153095

Can you join two tables or views using LIKE as the join operator as in:
Select x, y, z FROM TableA JOIN TableB ON TableA.Name LIKE TableB.Name
SQL Server accepts the SQL, but the query returns no results when I know there are records that would match the LIKE.
Background:
I have two tables with Company Names. Unfortunately they came from different sources so they're not formatted the same way. For example:
TableA.Name: XYZ, Inc TableB.Name: XYZ Incorporated.
The names in TableA are invariably shorter than those in TableB so I figured "Make a view that computes a new name field with all the spaces and punctuation squeezed out, add a '%' to the name in TableA and you can use LIKE to join the tables." However, it ain't working. I have:
ViewA.Name2: XYZINC% ViewB.Name2: XYZINCORPORATED
So if LIKE is valid then "Select * from ViewA as A JOIN ViewB as B ON A.Name2 LIKE B.Name2" should work. No such luck.
If LIKE is not supported in a JOIN does anyone have an idea how to merge the data to a new table using a set-based approach? I've been playing with subqueries to create a single table with the merged data as an alternative approach but so far no luck...

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 1

try

Select A.*,B.* from ViewA as A JOIN ViewB as B

ON B.Name2 LIKE A.Name2

Kindest Regards,

Vasc

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 2

That's basically what I've been doing with no luck. Instead of theory here's the real stuff:

Sample data from vw_CRM_Accounts:

[Company Name] DAM Squeezed

A O Smith

Lanphere Wendy

AOSMITH%

A O Smith

Lanphere Wendy

AOSMITH%

AARP

Biles Mike

AARP%

AAS/Chaas Acquisitions

Saunders Carolyn

AASCHAASACQUISITIONS%

ABX Air Inc

Vanden Heuvel Cheryl

ABXAIRINC%

ABX Air Inc

Vanden Heuvel Cheryl

ABXAIRINC%

Acco/Wilson

NULL

ACCOWILSON%

ACCURIDE CORPORATION:

Blanton Cindy

ACCURIDECORPORATION:%

ACF Industries, Inc.

McCallum Mike

ACFINDUSTRIESINC%

Sample data from vw_CDW_Accounts:

Acct_No Acct_Name Squeezed

42378 A A A /AMERICAN AUTOMOBILE ASSOC E AAAAMERICANAUTOMOBILEASSOCE

77216 A A R P E AARPE

77213 A A R P / FOUNDATION GRANTS / SCSEP E AARPFOUNDATIONGRANTSSCSEPE

42475 A C F INDUSTRIES INC E ACFINDUSTRIESINCE

47156 A D P TOTALSOURCE INC. W ADPTOTALSOURCEINCW

77203 A M R CORPORATION S AMRCORPORATIONS

78008 A S C INC C ASCINCC

77272 A T & T E ATTE

42606 A T & T ET AL E ATTETALE

Sorry for the small print but the CDW names are much longer...

Here's the query:

select

A.[Company Name], A.DAM, B.Acct_No, B.Acct_Name

from

vw_CRM_Accounts as A JOIN vw_CDW_Accounts as B

ON

A.Squeezed LIKE B.Squeezed

WHERE

B.Acct_Name LIKE 'A%'

I limited it to accounts starting with "A" just to keep the run time short. THey're both large views.

Unless I'm missing something I would expect to get matches "AARP%" LIKE "AARPE" and "AARPFOUNDATION...". I get no rows returned.

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 3

declare @a table(name varchar(50))

declare @b-2 table(name varchar(50))

insert into @a

select 'AOSMITH%' UNION ALL

select 'AARP%' UNION ALL

select 'AASCHAASACQUISITIONS%' UNION ALL

select 'ABXAIRINC%' UNION ALL

select 'ACCOWILSON%' UNION ALL

select 'ACCURIDECORPORATION:%' UNION ALL

select 'ACFINDUSTRIESINC%'

insert into @b-2

select 'AAAAMERICANAUTOMOBILEASSOCE' UNION ALL

select 'AARPE' UNION ALL

select 'AARPFOUNDATIONGRANTSSCSEPE' UNION ALL

select 'ACFINDUSTRIESINCE' UNION ALL

select 'ADPTOTALSOURCEINCW' UNION ALL

select 'AMRCORPORATIONS' UNION ALL

select 'ASCINCC' UNION ALL

select 'ATTE' UNION ALL

select 'ATTETALE'

select a.name,b.name from @a a join @b-2 b on

b.name like a.name

as posted before try:

select A.[Company Name], A.DAM, B.Acct_No, B.Acct_Name

from

vw_CRM_Accounts as A JOIN vw_CDW_Accounts as B

ON

B.Squeezed LIKE A.Squeezed

WHERE

B.Acct_Name LIKE 'A%'

Kindest Regards,

Vasc

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 4

rschaeferhig

SSCrazy Eights

Points: 8776

July 19, 2006 at 12:12 pm

#650190

Duh..

Thanks. That worked.

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 5

Related question:

How do I get the rows in B that are not matched by a row in A?

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 6

select

Distinct A.[Company Name], A.DAM, B.Acct_Number, B.Acct_Name,

A

.Squeezed, B.Squeezed

from

vw_CRM_Accounts as A right JOIN vw_CDW_Accounts as B

ON

B.Squeezed LIKE A.Squeezed

Where B.Acct_Name IS NULL

I knew there was an answer buried in there somewhere...

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 7

Ooops...

Select Distinct A.[Company Name], A.DAM, B.Acct_Number, B.Acct_Name,
A.Squeezed, B.Squeezed
from
ON
Where A.[Company Name] IS NULL

gives me rows in B with no match in A...

Robert Davis One Orange Chip Points: 28027 More actions · Answer 8

If you do a join using LIKE without using any wildcards, it is the same as doing the join using an = instead of LIKE ..... only slower. So, all you have achieved is making it slower.

Do it like this:

Select Distinct A.[Company Name], A.DAM, B.Acct_Number, B.Acct_Name,

A.Squeezed, B.Squeezed

from

vw_CRM_Accounts as A right JOIN vw_CDW_Accounts as B

ON

B.Squeezed LIKE '%' + A.Squeezed + '%'

Where A.[Company Name] IS NULL

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 9

The value of A.Squeezed already contains the wildcard (see examples above).

Robert Davis One Orange Chip Points: 28027 More actions · Answer 10

You don't need to create a view with the wildcards built in. You can do it like I did above. Using a view like this would be comparable to pushing in a thumbtack with a sledgehammer.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]