DSXchange

Posted: **Fri Mar 02, 2007 3:11 am**

Hi,
I have a column name is Full name
i want to split the full name into three columns
First name,middlename,last name.

Here is the data,
john bbb colian
sam donald

1. first row is contains the first name ,middlename,last name.
2.second row is first name,lastname

my source data is sequential file.

i tried with substring. it is not reading the value.
please give some suggestions

Posted: **Fri Mar 02, 2007 3:16 am**

There are several ways, but what is the logic on the second record?
Either "donald " goes to middle name or last name??

Posted: **Fri Mar 02, 2007 3:17 am**

john bbb colian
sam donald

only thing is we need to read the data. we already validating the data.

sam is first name
donald is last name

where ever we have the middle name...we need to dispay the name

Posted: **Fri Mar 02, 2007 3:24 am**

In transformer,

Code: Select all

Stage variable : vCount = Dcount(Input.Field,' ')

Use the following derivation for each column.

Code: Select all

Firstname : Field(Input.field,' ',1) 
MiddleName : If vCount > 1 Then Field(Input.field,' ',2)  Else ''
LastName : If vCount > 1 Then Field(Input.field,' ',3)  Else Field(Input.field,' ',2)

Posted: **Fri Mar 02, 2007 5:00 am**

On a point of efficiency, you've calculated Field(Input.Field, " ", 2) twice. It would therefore be better to calculate this in a stage variable. Once.

Posted: **Fri Mar 02, 2007 8:26 pm**

Yup... it will be certainly efficient.
I had a different approach when started, but ended with this.

Suresh, try using this

Stage variable :

Code: Select all

vCount = Dcount(Input.Field,' ')
vField2 =  Field(Input.field,' ',2)

Use the following derivation for each column.

Code: Select all

Firstname : Field(Input.field,' ',1)
MiddleName : If vCount > 1 Then vField2 Else ''
LastName : If vCount > 1 Then Field(Input.field,' ',3)  Else vField2

Posted: **Wed Mar 28, 2007 12:02 pm**

What if the data looked like the below?

colian,john bbb
donald,sam

1. first row contains the last name,first name' 'middle_initial
2.second row is lastname,firstname' 'middle_initial

I can grab the last name easy enough but is there away to tell it to grab whats to the right of the first comma it sees for the first name? I don't see away around having the firstname and middle_initial winding up in the firstname field but can that work.

Thanks

Posted: **Wed Mar 28, 2007 12:56 pm**

The Field function will do that for you automatically. It will only see two comma delimited values, so the space won't bother it. You could then use Field again on the 'first middle' value with a space as the delimiter to split it again... if only it was as simple as that.

Posted: **Wed Mar 28, 2007 1:03 pm**

Stage Var:

Code: Select all

sVlast = Field(Input.field,',',1)
sVfirst = Field(Input.field,',',2)
sVcount = Dcount(sVfirst,' ')

Column Derivations:

Code: Select all

Firstname : If sVcount = 1 then Field(sVfirst,' ',1) else sVfirst
MiddleName : If sVcount=1 then Field(sVfirst,' ',2) Else '' 
LastName : sVlast

IHTH

Posted: **Wed Mar 28, 2007 1:24 pm**

I actually understand that and wish I could have figured it out. Thanks for the help.

Posted: **Wed Mar 28, 2007 1:34 pm**

But as I said, it's never as simple as that.

Smith, Bobby Jo

Do you really want 'Jo' as the middle name or initial?

Posted: **Wed Mar 28, 2007 1:37 pm**

Luckily these are unedited fields so the best we can do is get a lastname and a firstname. I am surprised by some of the things they have in there

Posted: **Wed Mar 28, 2007 2:09 pm**

"Things" Like what

Posted: **Wed Mar 28, 2007 2:49 pm**

You probably don't want to know.

If it's like most systems I've seen, it could be - literally - anything.

Posted: **Wed Mar 28, 2007 2:54 pm**

"Anything" makes more sense

. Now i really don't want to know anything.

DSXchange

string splitting

string splitting