HOW FIELD FUNCTION WORKS IN DATASTAGE?

We can use the Field function in DataStage to return one or more sub strings that are located between specified delimiters in string.

Syntax:

Field(%string%,%delimiter%,%occurrence%,[%number%])

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 have below 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.

Comments

comments