LIKE operator between two tables 1

[redaccess]

I want to compare names in two tables but haven't found the best way to accomplish this. I know it's not a perfect science but I thought the LIKE operator would be a good start.

C_Name C2_Name

Darin Samuel
Sam Darren
Frank Bill


I'd like to compare the names in these two tables and list the names from C_Name that are close to any name in C2_Name.

Any suggestions?

 

[lameid]

Select C_Name.*
From C_Name, C2_Name
Where C_Name.Fname Like (Left(C2_Name.Fname,3) & "*&quot

This assumes Fname is the field name of each 'name' in the tables.

This is called a Non-equijoin because it does not join using the "=" operator.

 

[redaccess]

//I get the following error

Invalid column name '*'.

//when I run the code below with query analyzer:

Select checksource.*, checknames.*
From checksource, checknames
Where checksource.c_Remitname Like (Left(CheckNames.cn_name,3) & "*&quot

 

[lameid]

That's weird. The following executed for me

SELECT BILLTO.*, SHIPTO.*
FROM BILLTO, SHIPTO
WHERE (((BILLTO.BILL_NAME) Like (Left([SHIPTO].[CUST_NAME],3) & "*&quot) AND ((Len([CUST_NAME]))>3));

(Cust_name is empty in a lot of cases so I had to eliminate the empties)

Anyway try,

Select checksource.c_Remitname, CheckNames.cn_name
From checksource, checknames
Where checksource.c_Remitname Like (Left(CheckNames.cn_name,3) & "*&quot

Then switch back to design view to get the columns you want. You may need to add the len statement like I did.

FYI, All this query is doing is comparing to see if the first three letters are the same between the two fields.

Ultimately I'd build a table to map Darren and Darrin to one another. With a table you could also catch Dick and Richard, Bill and William (you get the idea).

 

[redaccess]

Ok, your code works (thank you), BUT only in MS ACCESS. If you try putting that code into the same scenario for SQL SERVER, it doesn't.

Interesting.

Thanks for the help.

 

[lameid]

That's because SQL Server and Access use different funtions to do the same thing. For example Access uses the NZ funtion where Transact SQL (SQL Server) uses Isnull and of course isnull in Access does something completely different.

A quick check on books online reveals that both the len and left function exist in SQL server... That leaves translating the Wildcard operator * to % and changing the concatenation operator & to +

So the following should work in SQL Server

Select checksource.c_Remitname, CheckNames.cn_name
From checksource, checknames
Where checksource.c_Remitname Like (Left(CheckNames.cn_name,3) + "%&quot

If not, post it to the appropriate SQL Server forum and ask what the problem is. Also post the thread reference here because I'd be curious to see the solution.

 

[redaccess]

This works,

Select checksource.c_Remitname, CheckNames.cn_name
From checksource, checknames
Where checksource.c_Remitname Like (Left(CheckNames.cn_name,3) + '%')

All I did was modify " to '.

Thanks for the help.

 

[lameid]

Oops... And I know better too... Single quotes instead of double quotes for strings and single quotes instead of hashes (#) for dates. Numbers work the same way, no delimiters. Something bit me in SQL server so I'll mention it...

Data Type Precedence:
Basically each datatype has a precedence or order of preference. If you divide an integer by an integer you will get an integer. So 12/5 equals 2 in Sql server where as 12.0/5 = 2.4

The topic is a little more involved than that but can keep you scratching your head until you sit down and read until the answer appears (I hate brute force knowledge-trial by ordeal).