Page 1 of 1
Need help in doing substring
Posted: Thu Aug 12, 2010 2:05 pm
by chrisjones
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.
Posted: Thu Aug 12, 2010 2:32 pm
by anbu
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]
Do you have Dr always in your input?
Posted: Thu Aug 12, 2010 3:03 pm
by kumar_s
Will you get Mr? If so, to be removed?
Will you First and Midle name after comma?
eg Shaw, Mr George Bernard
If so, how to handle?
Posted: Thu Aug 12, 2010 4:53 pm
by ray.wurlod
I would use the Standardize stage from the Data Quality folder applying the Canadian names rule set. But that requires having QualityStage licensed.
Need help in doing substring
Posted: Thu Aug 12, 2010 5:18 pm
by chrisjones
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
Posted: Thu Aug 12, 2010 5:32 pm
by arunkumarmm
You can use the code as suggested by Anbu with a little modification
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)
Posted: Fri Aug 13, 2010 2:57 am
by Sainath.Srinivasan
You can try this
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
Note - this is done in the fly. So do test thoroughly.
Need help in doing substring
Posted: Mon Aug 23, 2010 1:31 pm
by chrisjones
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.
Posted: Mon Aug 23, 2010 2:24 pm
by arunkumarmm
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)
Posted: Mon Aug 23, 2010 2:28 pm
by vinothkumar
combining count and Field function will give the results. Just tweak the code given by anbu.
Posted: Tue Aug 24, 2010 6:50 am
by srinivas.g
Last name Logic : field(DSLink3.name,",",1)
First name logic:
Substrings(convert(" ","",field(field(DSLink3.name,",",2),"(",1)),3,
Len(convert(" ","",field(field(DSLink3.name,",",2),"(",1))))
DSLink3.name contains the name.