Index Function

[pklcnu]

Hi

1) select * from Employee where name like '%ABC XYZ%'

2)select * from Employee where name like '%ABC%XYZ%'

In the second query there is extra % between 'ABC' and 'XYZ'.

Similar scenario in DataStage i am using Index funtion for string comparion for the above case1 , i am using some thing like

if Index(Name,"ABC XYZ",1)<>0 then 1 else 0

But my question is how should to deal with the case2 where we got extra '%' ?

I need to compare the substrings like where there can be any number of spaces between 'ABC' & 'XYZ' or '-' or '.' or ',' etc. between them.

I am not using the SQL queries any where, i just gave an example only.
Need to resolve through DataStage job.

Your will be much appreciated .

Thanks

[chulett]

You should investigate the Matches function. Search here or check the docs for the syntax.

[Sainath.Srinivasan]

You can achieve it in various ways.

One of them is via Matches as Craig has noted.

Sample

Code: Select all

If yourField Matches "0X'ABC'0X'XYZ'0X" Then .... Else ...

[pklcnu]

You can achieve it in various ways.

One of them is via Matches as Craig has noted.

Sample

Code: Select all

If yourField Matches "0X'ABC'0X'XYZ'0X" Then .... Else ...

...

Thanks very much for your quick response.....it is solved now....i used some thing like

If InputColumn matches '...ABC...XYZ...' then 1 else 0

Thanks

[chulett]

FYI - You could cut your derivation back to simply this and achieve the same result, no need to an IF-THEN-ELSE structure.

InputColumn matches '...ABC...XYZ...'