Comparing to character fields

[vanuta]

I have two tables I'm working with:

Table 1 looks like this:

Vendor_Name InvNumber Store Number Inv_Amt
Whirlpool Corp. 5001 450 2500.00
Cooper Tools 5002 609 3800.00
Cooper Tire 4500 501 8000.00
Amana 8011 605 9000.00

Table 2 looks like this:

Ven_Pricing_Name Loc_Code Volume
Whirlpool Corp. Finance 25 25000.00
Whirlpool America 30 800000.00
WhirlPool Industries 28 87000.00
Cooper Tools Inc. 69 98000.15
Cooper Tire of America 71 58000.69
Cooper Tire Worldwide 85 87000.69
Amana Refrigeration 62 52000.63
Amana of America 89 97000.45
Amana Finance Corp. 87 74000.25

I need to query out of table 2 any vendors that have almost the same vendor name with table 1.

Example:

All of Whirlpool from table 2 matching the name Whirlpool Corp. from table 1.

The fields are formatted like this:

Vendor_Name(varchar(25,null)
Ven_Pricing_Name(char(35,null)

Any help would be appreciated.

 

[Andel]

Hello, try this one...

select distinct b.* from table2 b, table1 a
where a.Vendor_name in (select substring(b.Ven_Pricing_Name, 1, len(a.Vendor_Name))from table1)

Andel
andel@barroga.net

http://andyb.8m.com