string splitting
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 160
- Joined: Tue May 02, 2006 7:49 am
string splitting
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
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
-
- Participant
- Posts: 160
- Joined: Tue May 02, 2006 7:49 am
In transformer,
Use the following derivation for each column.
Code: Select all
Stage variable : vCount = Dcount(Input.Field,' ')
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)
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Yup... it will be certainly efficient.
I had a different approach when started, but ended with this.
Suresh, try using this
Stage variable :
Use the following derivation for each column.
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)
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
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
-
- Participant
- Posts: 51
- Joined: Mon Feb 05, 2007 1:53 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
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
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
Stage Var:
Column Derivations:
IHTH
Code: Select all
sVlast = Field(Input.field,',',1)
sVfirst = Field(Input.field,',',2)
sVcount = Dcount(sVfirst,' ')
Code: Select all
Firstname : If sVcount = 1 then Field(sVfirst,' ',1) else sVfirst
MiddleName : If sVcount=1 then Field(sVfirst,' ',2) Else ''
LastName : sVlast
I haven't failed, I've found 10,000 ways that don't work.
Thomas Alva Edison(1847-1931)
Thomas Alva Edison(1847-1931)
-
- Participant
- Posts: 51
- Joined: Mon Feb 05, 2007 1:53 pm
-
- Participant
- Posts: 51
- Joined: Mon Feb 05, 2007 1:53 pm
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA