We can use the Field function in DataStage to return one or more sub strings that are located between specified delimiters in string.
delimiter evaluates to any character, including field mark, value mark, and sub value marks. It delimits the start and end of the sub string.
If delimiter evaluates to more than one character, only the first character is used. Delimiters won’t be returned with the sub string in the output.
occurrence specifies which occurrence of the delimiter is to be used as a terminator. If occurrence is less than 1, 1 is assumed automatically.
num.substr specifies the number of delimited sub strings to return. If the value of num. substr is an empty string or less than 1, 1 is assumed automatically.
When more than one sub string is returned, delimiters are returned along with the successive sub strings.
If we do not specify anything in place of delimiter or occurrence Field function in DataStage behaves as following way.
If either delimiter or occurrence is not in the string, an empty string is returned, unless occurrence specifies 1. If occurrence is 1 and delimiter is not found, the entire string is returned. If delimiter is an empty string, the entire string is returned.
If string evaluates to the null value, null is returned. If string contains CHAR(128) (that is, @NULL.STR), it is treated like any other character in a string. If delimiter, occurrence, or num.substr evaluate to the null value, the FIELD function fails and the program terminates with a run-time error message.
You can find the Field function in the below path.
Function > String > Field
A sample job to explain Field function
Let me show the usage of field function using one sample job.
Below my sample input data.
empno,name,dept_details,salary 123,Naveen,10|Accounts|Chicago|Illinois,10000 234,Bob,20|Technology|Dallas|Texas,20000 456,Max,10|Accounts|Chicago|Illinois,30000 567,Antony,20|Technology|Dallas|Texas,10000
Here ‘dept_details’ column is the combination of department id, department name, department city and department state.
My requirement is to populate all these department details in separate columns. This type of requirements can be achieved by field function in datastage.
So my expected output would be like:
eid,ename,depid,deptname,deptcity,deptstate,salary 234,Bob,20,Technology,Dallas,Texas,20000 567,Antony,20,Technology,Dallas,Texas,10000 123,Naveen,10,Accounts,Chicago,Illinois,10000 456,Max,10,Accounts,Chicago,Illinois,30000
Following job design used to achieve above expected output.
I created sample data as in input and I read it through sequential file stage.
In Transformer stage, I applied Field function on dept_details column to get the required details.
Field(before_xfrm.dept,'|',1) - depid Field(before_xfrm.dept,'|',2) - deptname Field(before_xfrm.dept,'|',3) - deptcity Field(before_xfrm.dept,'|',4) - deptstate
After running this job, I got below output.