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
How do i extract only data that is inside the commas
Moderators: chulett, rschirm, roy
How do i extract only data that is inside the commas
-------------------------
https://www.ssa.co.za
https://www.ssa.co.za
Re: How do i extract only data that is inside the commas
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.
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 would return the string 'fee'.
Is that what you are looking for?
But
Code: Select all
FIELD('"Deutche Bank",fee,Income from Deutche bank', ',', 2)
Is that what you are looking for?
-
- Participant
- Posts: 132
- Joined: Wed Mar 01, 2006 11:12 pm
- Location: Pune
Re: How do i extract only data that is inside the commas
BIuser wrote: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"
SMB