Need help in doing substring
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 194
- Joined: Thu May 11, 2006 9:42 am
Need help in doing substring
Hi ,
We are getting name : Zinman, Dr Bernard (Mount Sinai Hospital, Toronto) from source system.We need to separate the name into first name and last name with below logic.
Last Name logic:Before , is the last name i:e from above example the output should be Zinman
First Name logic: ignore the data in parenthsis() and remove Dr or replace Dr with space but the output should be Bernard from above example..
Please help with your ideas how can we get this.
We are getting name : Zinman, Dr Bernard (Mount Sinai Hospital, Toronto) from source system.We need to separate the name into first name and last name with below logic.
Last Name logic:Before , is the last name i:e from above example the output should be Zinman
First Name logic: ignore the data in parenthsis() and remove Dr or replace Dr with space but the output should be Bernard from above example..
Please help with your ideas how can we get this.
Thanks,
Chris Jones
Chris Jones
Code: Select all
Nm:Zinman, Dr Bernard (Mount Sinai Hospital, Toronto)
svLastNm=Field(Nm,",",1)
svFirstNmTmp=Field(Field(Nm,",",2),"(",1)
svFirstNm=Convert(" ","",svFirstNmTmp)[3]
You are the creator of your destiny - Swami Vivekananda
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 194
- Joined: Thu May 11, 2006 9:42 am
Need help in doing substring
Thanks for your reply...
Some records might have Dr or Not..some records might have MD also please suggest how to handle this ..
Ray..We don't have quality stage license..so can you suggest how to handle this requirement in datastage 8.1
Some records might have Dr or Not..some records might have MD also please suggest how to handle this ..
Ray..We don't have quality stage license..so can you suggest how to handle this requirement in datastage 8.1
Thanks,
Chris Jones
Chris Jones
-
- Participant
- Posts: 246
- Joined: Mon Jun 30, 2008 3:22 am
- Location: New York
- Contact:
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
You can try this
Note - this is done in the fly. So do test thoroughly.
Code: Select all
yourDelimiter = Convert('ABCD...Z ','',yourInput)
yourLastName = Field(yourInput, yourDelimiter[1,1], 1)
yourInputRest = yourInput[Index(yourInput, yourDelimiter[1,1]),99999]
yourFirstNameFull = Field(yourInputRest, yourDelimiter[2,1], 1)
yourFirstName = If (Index('|DR|MD|PROF|MR|MRS|MS|All Other Titles|','|' : Field(yourFirstNameFull,' ',1) : '|') > 0) Then Field(yourFirstNameFull,' ', 2, 99999) Else yourFirstNameFull
-
- Participant
- Posts: 194
- Joined: Thu May 11, 2006 9:42 am
Need help in doing substring
Hi,
Yes we will have somtimes: professor or PhD or Mr or Dr or M.D before the first name so I need to get only the first name can you please suggest how to get this.
Yes we will have somtimes: professor or PhD or Mr or Dr or M.D before the first name so I need to get only the first name can you please suggest how to get this.
Thanks,
Chris Jones
Chris Jones
-
- Participant
- Posts: 246
- Joined: Mon Jun 30, 2008 3:22 am
- Location: New York
- Contact:
This code should work. Did you try this?
Code: Select all
Nm:Zinman, Dr Bernard (Mount Sinai Hospital, Toronto)
svLastNm=Field(Nm,",",1)
svFirstNm=Field(Field(Field(Nm,",",2),"(",1),' ',Count(Field(Field(Nm,",",2),"(",1),' ')+1)
Arun
-
- Participant
- Posts: 342
- Joined: Tue Nov 04, 2008 10:38 am
- Location: Chennai, India
-
- Participant
- Posts: 251
- Joined: Mon Jun 09, 2008 5:52 am