I have a field in the following format:
50% (ABC: $1,000)
100% (ABC: $5,000)
I want to parse this into 2 columns so that the result looks as follows
A B
50% $1,000
100% $5,000
Is there a function in data stage or a combination of functions where I can look for a % or a $ and then take everything to the left of the percentage sign or everything to the right of the $ sign.
Thanks
Aaron
Parsing a Varchar Field
Moderators: chulett, rschirm, roy
INDEX(MyString,'$',1) returns the position of the first $ in the string.
But in your case I might use:
PercentageValue = FIELD(In.StringVar,'%',1)
DollarValue = FIELD(In.StringVar,'$',2)
This treats the % and $ signs as if they were field separators and, to use the example of '%', it parses the string into 2 fields "50" and " ABC: $1000)". This is an easy way to parse strings, but using the INDEX() function and specific substring lengths is more efficient at runtime.
But in your case I might use:
PercentageValue = FIELD(In.StringVar,'%',1)
DollarValue = FIELD(In.StringVar,'$',2)
This treats the % and $ signs as if they were field separators and, to use the example of '%', it parses the string into 2 fields "50" and " ABC: $1000)". This is an easy way to parse strings, but using the INDEX() function and specific substring lengths is more efficient at runtime.
</a>