Field Function in 8.5

[monishab]

Hi,
Am a newbie to Datastage and am trying to make use of Filed fucntion to extract the values from a string.
Look for the first forward slash starting from the right of the string and take 4 characters to the left of the forward slash

Example Data:
NTCADTX CAD 3333/ 5930/1 ---- 5930
NTSOLDI EUR 2356/ 0001/24 ---- 0001
NTCADTX USD /478568/123/5930/1 --- 5930

Code: Select all

If IsNotNull(lnk_read_gxm_src.agent) OR Len(TrimLeadingTrailing(lnk_read_gxm_src.agent)<>0) then Right(Field(lnk_read_gxm_src.agent,'/',1) ,4) Else '-1'

Above expression pulls data from First '/' only, can any one help me pulling the correct instance which is values before last '/'.

What would be the correct expression for this ?

[ray.wurlod]

Welcome aboard.

To solve this one you need to specify the last field number as the third argument of the Field() function. This is most easily found by counting the delimiter characters - and adding one if you want the very last field.

Code: Select all

Right(Field(lnk_read_gxm_src.agent, "/", Count(lnk_read_gxm_src.agent, "/")), 4)

[chulett]

As noted, there can be four arguments passed to the Field function, check the documentation. That optional one would be helpful here.