String pattern matching in lookup stage!!!

[kiranredz]

Hi,

I have a requirement i am doing a lookup based on strign field and am suppose to use the contains logic to match.

Ex: If my source is "washington" and the value in the lookup is "washington DC USA", these records should match. Actually these are unmatched records.

But the requirement is that these should be matched similar to the string pattern matching using "like" in oracle.

How can I implement this requirement?
I already have a lookup stage where am looking up on the same fields mentioned above. if this requirement of mine can be implemented in the same lookup, it would be great.

I cannot take a substring of the data and do the lookup(Ex: Field[1,5]). Coz, if i restrict the data to certain length, i'll have duplicates from the lookup which would fetch me an invalid value.

Thanks in advance,

[Romy]

is that enough if you match the first word(whatever length) of the string.... will it be ok for your requirement.

[ray.wurlod]

In version 7 the only possible normal lookup you can do with a Lookup stage is an equality lookup (an exact match is required). You might contemplate using a sparse lookup that includes a LIKE condition in user-defined SQL. But it won't be fast.

[kiranredz]

is that enough if you match the first word(whatever length) of the string.... will it be ok for your requirement.

yes. Thats fine. how can we do that?

[kiranredz]

In version 7 the only possible normal lookup you can do with a Lookup stage is an equality lookup (an exact match is required). You might contemplate using a sparse lookup that includes a LIKE condit ...

hi ray,
Am working on version 7. Could you pls explain me how i can implement this using sparse lookup?

I know how a sparse lookup woks. I just wanted to know whether i've to include some logic using SQL.

[ray.wurlod]

Use user-defined SQL in the database stage that services the reference link.

(Sparse lookup is not available for all database types.)