Comparing like column strings in two tables

[mrknifeguy]

Hello, I am trying to do something out of the ordinary. I have searched the forums to no avail.

I have two SQL tables containing inventory data of the same items but the descriptions are not exactly the same (one table is from an insurance company, the other from an independent contractor). I need to reconcile the item numbers from one table with the inventory numbers of the same items in the other table.

This is the TSQL I came up with:

SELECT table1.ItemNum, table1.Descript ion, table2.Descript ion, table2.ItemNum
FROM table1 CROSS JOIN table2
WHERE (table1.Descrip tion LIKE table2.Descript ion)

This only returns rows where the descriptions in both tables are identical. I would like to be able to use wildcards to indicate that I will accept records where the description of one table is contained in the other.

I have 5000 rows to compare....Help ?

[davef]

Hello, I am trying to do something out of the ordinary. I have searched the forums to no avail.

I have two SQL tables containing inventory data of the same items but the descriptions are not exactly the same (one table is from an insurance company, the other from an independent contractor). I need to reconcile the item numbers from one table with the inventory numbers of the same items in the other table.

This is the TSQL I came up with:

SELECT table1.ItemNum, table1.Descript ion, table2.Descript ion, table2.ItemNum
FROM table1 CROSS JOIN table2
WHERE (table1.Descrip tion LIKE table2.Descript ion)

This only returns rows where the descriptions in both tables are identical. I would like to be able to use wildcards to indicate that I will accept records where the description of one table is contained in the other.

I have 5000 rows to compare....Help ?

You probably don't need a CROSS join here. Try something along the lines of:

SELECT     table1.ItemNum, table1.Description, table2.Description, table2.ItemNum
FROM         table1, table2
WHERE     '%' + table1.Description '%' LIKE '%' + table2.Description + '%'

[mrknifeguy]

THANKS! That's it! I was just looking for a way to concatenate the wildcard characters to column names...duh!

[sapphirerus]

Hm... Seems this not work anyway in MySQL..

In MySQL you must use:

[B]SELECT[/B]     table1.ItemNum, table1.Description, table2.Description, table2.ItemNum
[B]FROM[/B]         table1, table2
[B]WHERE[/B]      table1.Description LIKE CONCAT('%', table2.Description, '%')