Basically the logic is that the related data (Paired) is seperated by semi colon
The pair is seperated by a "+" sign
Sometimes the second part of the so called Paired data could be missing.
The order of the pairs is not fixed.
I was envisioning either a Oracle function or Datastage Routine, which can search in the string for the first part and give me the second part of the pair (First part could be passed as an input parameter)
Please guide
Consider a server job with a server routine. DataStage BASIC has a wonderful richness of string-handling functions, because everything it does it does on strings.
FUNCTION ReturnSecondOfPair(aString, aSearchString)
* Convert string into dynamic array.
String = Convert(";", @FM, aString)
* Find search string within string
Find aSearchString In String Setting FMC,VMC,SMC
Then
Ans = Field(String<FMC>, "+", 2, 1)
End
Else
Ans = ""
End
RETURN(Ans)
Error handling (such as null argument handling) has been omitted for clarity.
Last edited by ray.wurlod on Thu Mar 04, 2010 2:35 am, edited 1 time in total.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
And finally in the Derivation Tab, we can fetch the "other part" of the string by using the derivation - OtherPart = SVarNewString[SVarIndexPairSeparator+1,SVarIndexValueSeparator - SVarIndexPairSeparator]
Obviously there will be some cases where it won't work, where the search string occurs earlier in the overall string than where it's the first element. But none of your examples had that situation.
The solution provided by vkhandel suffers from the identical limitation.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
FUNCTION ReturnSecondOfPair(aString, aSearchString)
* Convert string into dynamic array.
String = Convert(";", @FM, aString)
* Find search string within string
FindStr aSearchString In String Setting FMC,VMC,SMC
Then
Ans = Field(String<FMC>, "+", 2, 1)
End
Else
Ans = ""
End
RETURN(Ans)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
It works fine now! Not sure what went wrong with me last time.
Thanks you so much Ray!!!
It worked for all scanario for me, can you please let me know the limitation you said this had, so I can be aware of it.
Dsnew wrote:It worked for all scanario for me, can you please let me know the limitation you said this had, so I can be aware of it.
What he means is if you have your search string as a sub-string elsewhere in your name/value list and that substring appears prior to that actual search string e.g.
Stair+1;air+2;
With search string of "air" would get you the 1 and not the 2.
You can for the most cut this off by searching for "<name>+" (this will not work for above example) or almost entirely by searching for ";<name>+" but will need separate handling for the first name/value pair
Umm... not so, because the ";" characters have become field marks, and FindStr and related statements don't include these as data values. However, a trailing "+" would do the trick.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Ah... missed that little nuance. Still, bracketing your search string with the internal delimiters can be a handy little trick for stopping substring matches. Generally.
-craig
"You can never have too many knives" -- Logan Nine Fingers
Kryt0n wrote:What he means is if you have your search string as a sub-string elsewhere in your name/value list and that substring appears prior to that actual search string e.g.
Stair+1;air+2;
With search string of "air" would get you the 1 and not the 2.
You can for the most cut this off by searching for "<name>+" (this will not work for above example) or almost entirely by searching for ";<name>+" but will need separate handling for the first name/value pair
ray.wurlod wrote:However, a trailing "+" would do the trick. ...
How do we handle the above screnario?
Like already mentioned above adding a trailing "+" gives 1 and not 2.
Any help appreciated.
FUNCTION ReturnSecondOfPair(aString, aSearchString)
* Convert string into dynamic array.
String = Convert(";", @FM, aString)
SearchString = aSearchString : "+"
* Find search string within string
FindStr SearchString In String Setting FMC,VMC,SMC
Then
Ans = Field(String<FMC>, "+", 2, 1)
End
Else
Ans = ""
End
RETURN(Ans)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.