matching contents somewhere in a free-format field

[Guest_imported]

How can I match a reference field from one table
with another table’s field which holds the reference somewhere within the field ?

eg Table 1 PaymentNo Amount
0001 10
0002 50

Table 2 Account Description
Acc1 Payment from 08/21/2002 Number 0002
Acc2 Pymt from 8/1/02 No, 0001

I want to match Table 1’s PaymentNo with the numbers in Table 2’s Description,
which as you can see is free-format text, to return
Acc1 50
Acc2 10

Is this possible ?

 

[JMCraig]

Something like this would certainly get you candidates. I can't see any way to guarantee that you have the intended relationship with 100% reliability:

select Table2.Account, Table1.Amount
from Table2, Table1
where Table2.Description
like '%' + convert(varchar, Table1.PaymentNo) + '%'

I'm assuming that Table1.PaymentNo is a numeric value. If it's a varchar, then you don't need the CONVERT function.

At any rate, see what that does for you.

HTH,

John J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net