Need help in doing substring

[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.

[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?

[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?

[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.

[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

[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)

[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.

[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.

[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) 

[vinothkumar]

combining count and Field function will give the results. Just tweak the code given by anbu.

[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.