Using LIKE in a JOIN query

Question

I have two separate data tables.

This is Table1:

Customer Name  Address 1       City     State  Zip
ACME COMPANY   1 Street Road   Maspeth  NY     11777

This is Table2:

Customer   Active Account   New Contact
ACME       Y                John Smith

I am running a query using the JOIN where only include rows where the joined fields from both tables are equal.

I am joining Customer Name from Table1 and Customer from Table2. Obviously no match. What I am trying to do is show results where the first 4 characters match in each table so I get a result or match. Is this possible using LIKE or LEFT?

Solution 1

Yes, that's possible. But I doubt, that every name in table 2 only has 4 letters, so here's a solution where the name in table2 is the beginning of the name in table1.

Concat the string with a %. It's a placeholder/wildcard for "anything or nothing".

SELECT
*
FROM
Table1
INNER JOIN Table2 ON Table1.CustomerName LIKE CONCAT(Table2.Customer, '%');

Concatenating of strings may work differently between DBMS.

Solution 2

It probably is, though this might depend on the Database you are using. For example, in Microsoft SQL, it would work to use somthing like this:

SELECT *
FROM [Table1] INNER JOIN [Table2]
ON LEFT([Table1].[Customer Name],4) = LEFT([Table2].[Customer],4)

Syntax may be different if using other RDBMS. What are you trying this on?

Solution 3

Seems like this should work:

Select *
From Table1, Table2 
Where Table1.CustomerName Like Cat('%',Trim(Table2.CustomerName),'%')