DSXchange

Posted: **Wed Oct 24, 2007 6:19 pm**

Hi Everyone I have got new problem,
Earlier I thought of first name and last name only
Now I have prob with middle name
For example:
From the source I am getting a name called "kenneth charlie Browne"
in the transformer the name should get split into only two columns called first name and last name
In the first name column "kenneth"
In the last name column"Browne"

Can any one throw soe light on this
I am using Index and field function
but in the last name column I am getting "charlie browne" but I want only the last name"Browne"

thanks for your time

Posted: **Wed Oct 24, 2007 6:25 pm**

Post your derivations.

Posted: **Wed Oct 24, 2007 6:27 pm**

Post your derivations.

Posted: **Wed Oct 24, 2007 6:31 pm**

Use

Code: Select all

DCOUNT(In.ColumnName,' ')

to get the number of space-delimited words, then use

Code: Select all

FIELD(In.ColumnName,' ',DCOUNT(In.ColumnName,' '))

to get the last "word".

p.s. Corrected mistyping of the FIELD function, the original post mistakenly used INDEX. Thanks JoshGeorge for bringing that up.

Posted: **Wed Oct 24, 2007 11:01 pm**

You can use Field function this way

Field({InputString},{Delimiter},{Position},[{NumberOfFields}])

Code: Select all

First Name: Filed(Input.Field,' ',1,1) 
Last Name: Field(Input.Field,' ',Count(Input.Field,' ')+1,1)