Probably the simplest means of accomplishing the extraction of one or the other values is to create a simply Datastage Transform that has three visible arguments: DataToParse (actual data value), Delimiter (such as space), and ValueToReturn (integer value 1,2,3, etc. to indicate which value you want returned). In the definition field of the transform insert the following command:
Field(Trim(%DataToParse%),%Delimiter%,%ValueToReturn%))
Once you've created this transform, you'll be able to use it in the derivation of your target column(s). Keep in mind that a transform has pros and cons: 1) pro: it is less expensive to use a transform then to call a routine because it is compiled as part data stage job's object. 2) con: if you change the transform you'll need to recompile any jobs that are using it.
Here is what the derivation column might look like if your data was "1234 + 5678" and the transform is named ParseData:
ParseData(linkname.column," ",1) returns 1234
ParseData(linkname.column," ",3) returns 5678
ParseData(linkname.column,"+",1) returns 1234b (b represents space)
ParseData(linkname.column,"+",2) returns b5678 (b represents space)
Hope this helps!
Mark
Abhinav wrote:Hi,
I have source files with one of the column which has values as
Code: Select all
Field1
12345 + 3456
12345 and 897698
234455 * 82379
783845 &*# 2938
I need to have my ouput into two columns with only numeric values into them, i should treat any special charcters as my delimitter.
My output should like
Code: Select all
Column1 Column2
12345 3456
12345 897698
234455 82379
783845 2938
Is this possible to accomplish through datastage in server edition if yes can you throw some ides on this.
I appreciate your help.
Thanks
Abhi