SQL: partial match with set of vsalues from a subquery

Dear All

I have a simple problem , how can a compare part of the substring with the set of values from a sub query.

here is the example, probem is at irem No: 3

1. The below SQL will be OK

  select a.code,b.name from tabA a,tabBb   where a.code=b.code and a.patternhere like '%I am in%"

2. The below one will also be OK (Exact match)

   select a.code,b.name from tabA a,tabBb    where a.code=b.code and a.patternhere

      IN ( select pattern from tabC c where c.pattern like '%I am in%')

3. How about the below one (Partial match)

      (taking the item No: 2 example. )
       if the subquery (IN clause ) elemnets contains the part of the
      matching    strings.
      Then how can i compare with this partial match
       DATA:
         a.patternhere='I am in'
         tabC


          'I am in TOKYO'

          'I am in NY'

          'I am in LONDON'

          'I am in PARIS'

          'You are in AU'

          'You are in NZ'

      --tabc contents
          

 Thanks in Advance Received on Tue Aug 09 2005 - 03:36:22 CDT