FIELD function

sam334 · Post by **sam334** » Fri Sep 25, 2015 8:18 am

All, need a quick help on field function.

I have a name- ASHLEY, MARTHA ANNIE

This is char(30) column and need to separate it to first name,last name and middle name, three separate column. Used FIELD function below.

LAST NAME- FIELD(name,',','1')

Last name worked. But got stuck in first and middle name.
It should be,
Last name- ASHLEY
First Name-MARTHA
Middle name-ANNIE

Appreciate your help.

chulett · Post by **chulett** » Fri Sep 25, 2015 9:00 am

So... the second comma delimited field is First and Middle. Pull that and then use Field again on that telling it is a space delimited string: 1=First and 2+=Middle. Now that won't always work but it should get you close enough so you can then identify the outliers.

ray.wurlod · Post by **ray.wurlod** » Fri Sep 25, 2015 3:45 pm

This is a case where I'd use stage variables to avoid unnecessary processing (in particular splitting the comma-delimited string more than once). For example:

Code: Select all

svFamilyName  <--  Field(InLink.FullName, ",", 1, 1)
svGivenNames  <--  Field(InLink.FullName, ",", 2, 1)
svFirstName  <--  Field(svGivenNames, " ", 1, 1)
svMiddleName <--  Field(svGivenNames, " ", 2, 9)

chulett · Post by **chulett** » Fri Sep 25, 2015 6:25 pm

Thought that went without saying.

ray.wurlod · Post by **ray.wurlod** » Sat Sep 26, 2015 4:36 am

One of the services I perform is a DataStage health check. You would be surprised how many functions are unnecessarily multiply evaluated.

sam334 · Post by **sam334** » Tue Oct 13, 2015 8:59 am

Worked well. I was stuck with the outliers. Solved now. Thanks a lot Craig and Ray. Appreciate your help.