string splitting

[suresh_dsx]

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

[kumar_s]

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

[suresh_dsx]

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

[kumar_s]

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)

[ray.wurlod]

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.

[kumar_s]

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

[travissolt]

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

[chulett]

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.

[us1aslam1us]

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

[travissolt]

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

[chulett]

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?

[travissolt]

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

[us1aslam1us]

"Things" Like what

[chulett]

You probably don't want to know.

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

[us1aslam1us]

"Anything" makes more sense . Now i really don't want to know anything.