Searching for %Value% in a lookup table

[in_finity307]

Hello,

I have a requirement to implement the following logic.

if column A from table A is like '%columnB%' from table B return target value from B

Here A is the primary table, B is the reference / lookup table.
This means that if column A from table A has the value 'AMBC' and column B from table B has some value 'MB', it matches and an output record is returned.

Table B is having some 20 total records.
Could you help me with implementing this logic in Datastage?

[srinivas.g]

Use instr sql function........ in source query

[in_finity307]

Thanks for the reply. But I solved it another way.
I inserted the values in table B by enclosing the column B values within the wildcard characters '%' . For example, instead of inserting 'MB', I inserted '%MB%' and then wrote the following SQL Query

Select * from A, B where A.A like B.B

I guess this is more of a SQL solution than Datastage but this is what worked.

[abhijain]

Yes, its always advisable to play in query, if you can, rather than implementing it in Datastage.

Please mark this thread as Resolved using the green button at the top of the page.

[mgendy]

Thanks for the reply. But I solved it another way.
I inserted the values in table B by enclosing the column B values within the wildcard characters '%' . For example, instead of inserting 'MB', I inserted '%MB%' and then wrote the following SQL Query

Select * from A, B where A.A like B.B

I guess this is more of a SQL solution than Datastage but this is what worked.

Does that mean that you have used sparse lookup instead of normal lookup ? please advice because i have the same issue and i cant use sparse lookup because the base is huge and the lookup table doesn't has indexes , also the lookup table is teradata table which isn't support sparse lookups

i'm waiting your feedback

[kbsuryadev]

Check the INDEX function in DataStage

[mgendy]

Check the INDEX function in DataStage

how to use index function within lookup stage , in order to lookup a portion of a string ? kindly illustrate

[in_finity307]

No, I haven't used a sparse lookup. I have included the lookup condition in the SQL Query itself. I haven't used a lookup stage in Datastage.

Check out the query I have posted earlier.

[kbsuryadev]

source>>>>>>trn( in transformer UPCASE(INDEX(Inputcolumn,'string',1))<>0 Then _________ else ______________ ) and then do a look up on the source row.

use upcase depending on your requirement.