How can I delete specific characters from a string variable?

[mlarruda]

Hi, I have a variable/column called NAME, where some names start with "Dr ", Mr" etc. and I want to delete this to make "Mr. James" become only "James", for example.

I did this subroutine:

SUB CleanName (SourceString)

IF Left(SourceString,3) = 'Mr '

SourceString = Right(SourceString,Len(SourceString)-3);

IF Left(SourceString,3) = 'Ms '

SourceString = Right(SourceString,Len(SourceString)-3);

IF Left(SourceString,4) = 'Mrs '

SourceString = Right(SourceString,Len(SourceString)-4);

IF Left(SourceString,3) = 'Dr '

SourceString = Right(SourceString,Len(SourceString)-3);

END SUB

And called it by typing

CALL CleanName(NAME);

in the last row of the Main tab, nut it didn't work.

Any friend can point me where is the error or how can I do what I want?

[stascher]

In your load statement for the column NAME, try the following:

Load

if(match(subfield(NAME,' ',1),'Mr','Ms','Mrs','Dr'),subfield(NAME,' ',2),NAME) as NAME

from..... 

[lcontezini]

if(left(SourceString,2)='Ms', replace(SourceString,'Ms ',''),

if(left(SourceString,2)='Mr', replace(SourceString,'Mr ',''),

if(left(SourceString,2)='Dr', replace(SourceString,'Dr ',''),

if(left(SourceString,3)='Mrs', replace(SourceString,'Mrs ','')))))

[mlarruda]

Thanks a lot, but how this code will work: Will a name like "Feodr Petrov" be changed to "Feo Petrov" or simply "Petrov"? I DON'T WANT that such changes happens, because here the "dr" is parte of the name rather than an abbreviation of "Doctor".

[mlarruda]

Thanks a lot, but this code will not avoid that names like "Feodr Petrov" be changed to "Feo Petrov". I DON'T WANT that such changes happens, because here the "dr" is parte of the name rather than an abbreviation of "Doctor".

[stascher]

It will not alter Feodr Petrov because subfield(1) returns Feodr which does not match any of the strings 'Mr','Mrs','Ms' or 'Dr'

[lcontezini]

This code will replace only if the initials of the name are 'Dr', 'Ms'...

In the case of "Feodr Petrov", the code left(SourceString,2) will return FALSE and there will be no replacing.

[mlarruda]

But if the string is "Dr Feodr Petrov" your code will replace both "dr", remaining "Feo Petrov"...

[zebhashmi]

your code looks good

can you use it in load

IF(Left(SourceString,3) = 'Mr ',Right(SourceString,Len(SourceString)-3), next one, as SourceString;

[mlarruda]

Thanks, but didn't work. The subfield(NAME,' ',2) is extracting only the 2nd word of the string. E.g. "Mr James Smith" is returning only "James" instead of "James Smith".