Usage of Field Function

[balajisr]

Is it correct to use field function with delimiter as a string
(e.g Field(string,'ABC DEF',2). and not as a single character?
I have a requirement to find values between two strings so thought of using field function for that.

E.g ABC DEF
This is the value to Extract
VVV EEE

where i need to extract values between ABC DEF and VVV EEE. Since my file is unstructured my record spans multiple lines.So, value to be extracted can also span multiple lines.

I tried to use string as delimiter and it works. Want to know whether it is advisable. Please advise.

Thanks.

[kcbland]

It's fine.

[AaronVG]

I use Field quite often.

I have source data that looks like this
part1@part2@part3

I use a Transformer to split the one column into three:
Field(in.col1,"@",1)
Field(in.col1,"@",2)
Field(in.col1,"@",3)

And the Transformer does about 5 other conversion and adds in two additional columns as well.

[boxtoby]

I'm not sure if I undertand correctly, but I think you're using a string like "ABC DEF" as the delimiter of the main string.

If you are, field my not return the results you're expecting. Here's an example:

String = "aaaa123bbbb123ccccc123dddd"
Ans = field(String, "123", 3)

Ans = 23ccccc !!

Notice in the previous example the separator is a single character, ie. "@"

You can solve this problem as follows:

String = "aaaa123bbbb123ccccc123dddd"
NewString = Ereplace(String, "123", "@")
Ans = field(NewString, "@", 3)

Ans = ccccc

HTH
Bob.

[balajisr]

If the delimiter of the Field function has multiple character it is able to find the correct value but it could not remove all characters in the delimiter while deriving resultant substring output. It removes only the first character from the delimiter and outputs the rest along with the resultant substring. i.e '23ccccc'.

Also, since the file is highly unstructured i do not want to replace multiple character delimiter to single character since the character to be replace may be present in the record already.

I will proceed using Field() function and handle the delimiter coming as part of resultant substring.

Thanks to everyone for your replies.