How do i extract only data that is inside the commas

[BIuser]

Hi all

I have a column with the below data format :

"Deutche Bank",fee,Income from Deutche bank
"Vodafone", margin, transfer

I need to extract only data inside the commas, I thought of using substrings, but i do not have the same number of characters inside the commas in other rows.

what can i use, or what code can i use to extract data only inside the commas

Thanks

[rsaliah]

You can use a Sequential file stage and define the field separator as ',' or if you like can parse the data through a transform stage and use the function FIELD.

[ArndW]

This looks like the classic use of the FIELD() function. I'm not sure exactly what you are trying to do ("between the commas" is vague).

But

Code: Select all

FIELD('"Deutche Bank",fee,Income from Deutche bank', ',', 2)

would return the string 'fee'.

Is that what you are looking for?

[baglasumit21]

Hi all

I have a column with the below data format :

"Deutche Bank",fee,Income from Deutche bank
"Vodafone", margin, transfer

I need to extract only data inside the commas, I thought of using substrings, but i do not have the same number of characters inside the commas in other rows.

what can i use, or what code can i use to extract data only inside the commas

Thanks

By "data inside the commas" if you mean data inside the double quotes " then if the field is always the first field in the row then you can used the function 'FIELD' to get it

For ex.
Suppose
myvar = "Deutche Bank",fee,Income from Deutche bank

then
Field(myvar, "," , 1) will return "Deutche Bank"