LOOKUP with partial string

BuddingDev · Post by **BuddingDev** » Thu Mar 01, 2012 12:30 pm

Hi,

Is their way to do lookup with partial string match?

Like I am looking for a presence of one word in a primary link rather than exact field match and consider it to be matched.

Thanks

ray.wurlod · Post by **ray.wurlod** » Thu Mar 01, 2012 2:50 pm

Not easy. You could try a sparse lookup with custom SQL using LIKE operator.

qt_ky · Post by **qt_ky** » Thu Mar 01, 2012 7:12 pm

I can confirm sparse lookup using LIKE condition does work with the DB2 Connector stage. Oracle also supports sparse lookups. It would be nice if you could do a wildcard match in the Lookup stage no matter what stage was on the reference link. As far as I know you can do exact matches, caseless matches, or range lookups, but not exactly wildcards...

kandyshandy · Post by **kandyshandy** » Thu Mar 01, 2012 9:40 pm

BuddingDev wrote:Like I am looking for a presence of one word in a primary link rather than exact field match and consider it to be matched.

You can, by this method. Suppose your one word is 'dummy'. Search both input and look up key column for this word & if found, populate a new column with value 'dummy'. For other records, populate this new column with original key value. Now do a look up using this new column as key and you can get that you need.

It's friday, so take it easy

kandyshandy · Post by **kandyshandy** » Thu Mar 01, 2012 9:45 pm

In fact, you can parameterize your word as well so that you don't have to edit your job when that word changes.

chulett · Post by **chulett** » Fri Mar 02, 2012 7:38 am

What makes you think there is only "one word" involved here?

qt_ky · Post by **qt_ky** » Fri Mar 02, 2012 2:25 pm

Kandy, are you suggesting using Index() and perhaps making 2 passes through the data? Sounds like that may work to match a partial string and it would simply add extra processing time. I'll have to try it "in my free time." I may have to start my own topic on that later, because I need to do wildcard lookups in real-time web service jobs with NO database I/O (i.e. no sparse lookups).

kwwilliams · Post by **kwwilliams** » Fri Mar 02, 2012 2:49 pm

Your best case is to write a custom operator to do this work. It may be possible to do wildcard lookups through a series of passes through the data -- but this will never be as effecient as a custom op to get the work done in real time or batch for that matter.

qt_ky · Post by **qt_ky** » Fri Mar 02, 2012 2:55 pm

Thanks for the tip, Keith. Goal will be to reach millisecond response time! Will have to test that!!

BuddingDev · Post by **BuddingDev** » Fri Mar 16, 2012 12:51 pm

Thank you all for your understandable or not quite understandable responses. :D

I appreciate your inputs but this issue's requirement has changed so I am good for now.

DSXchange

LOOKUP with partial string

LOOKUP with partial string

Re: LOOKUP with partial string