Matchfield fn not providing correct result in transformer

[saj]

Hi All ,
I am trying to use matchfield function for the below scenario .
My requirement is if Arg1 will contain SDN in any position it should be populated as Y else Null.

TRIM((IF MATCHFIELD (Arg1,"0X'SDN'0X",2)=1) THEN 'Y' ELSE @NULL)

Values for Arg1 is as follows
==================
TFM,SDN,SDDN
TFM,SDN
SDN

But when I give this condition in transformer column level constraint it is not giving the correct result .

Only when Arg1=SDN alone it is providing Y .

When Arg1=TFM,SDN,SDDN or TFM,SDN it is providing null

When I fire the same in Datastage Manager (routine) it is providing the correct result.

ie when Arg1=TFM,SDN,SDDN it is providing Y.

Later in an article I read that "in the column derivation will return 1 for a match 0 for no match"

Hence I tried modifying the same with the below condition.

If Index(MATCHFIELD(Arg1,"0X'SDN'0X",2),'1',1) Then Arg2 else @NULL

But again this also is not working .
I am getting the result same as above.

Please help .

[ray.wurlod]

That's because you don't understand the function.
MatchField(InLink.TheString, "0X'SDN'0X", 2) will only ever return 'SDN'.

What you probably want is

Code: Select all

If Index(InLink.TheString, 'SDN', 1) > = Then "Y" Else @NULL

which would be more efficient than

Code: Select all

 If MatchField(InLink.TheString, "0X'SDN'0X", 2) = "SDN" Then "Y" Else @NULL

[rameshrr3]

Do you really need a Trim Function there ? You are assigning "Y" or null anyway .

I presume its a column level constraint being used in a transformer column derivation , rather than a transformer constraint . I wouldn't use anything that returns a null value in a Transformer constraint or stage variable.

Maybe you can also try

Code: Select all

IF Arg1 MATCHES "...SDN..."  THEN 'Y' ELSE @NULL

Of course an String Like "ISDN" will have a positive match and return "Y"