DSXchange

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

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

You can achieve it in various ways.

One of them is via Matches as Craig has noted.

Sample

Sainath.Srinivasan wrote: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

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...'