Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a field called NameVZ which looks like this : F.M.J. Hof van den
where F.M.J. are the initials, Hof is the surname and "van den" the preposition.
I'd like to create two fields out of this. One with the Initials which is easy -> SubField(NameVZ, ' ',1) as Initials
The other field should have preposition + Surname in one: "van den Hof"
But how do I do that as the propostion can be different for each name?
So to be clearer:
Field NameVZ (examples)
F.M.J. Hof van den
T. Johnson
S. Haaren van
B. Beuker de
should become
Field FullnameVZ
van den Hof
Johnson
van Haaren
de Beuker
Preposition before the surname
Thx Michael,
Your suggestion helped me on the right track.
This is what I've used now:
If(len(Trim(TextBetween(TextBetween(NaamVZ, ' ',''),' ','')))= 0, SubField(NaamVZ, ' ', 2), TextBetween(TextBetween(NaamVZ, ' ',''),' ','') & ' ' & SubField(NaamVZ, ' ', 2)) as AchternaamVZ
It's not 100% waterproof (e.g. da Silva de Jesus - > Silva de Jesus Da) but it will do.
How about looping through a set of known name prepositions then. Something like this:
SET vName ='';
SET vEndName = textbetween(NameVZ, ' ', '');
FOR EACH vPrep IN ' van den',' van der',' van de',' de',' den'
if Lower(Right(vEndName,Len(vPrep))=vPrep THEN
SET vName = vPrep & textbetween(vEndName, ' ', ''),
EXIT FOR
ENDIF
NEXT
IF Len(vName)=0 THEN
SET vName =vEndName
ENDIF
Note:
1. the spaces at the start of each of the listed vPrep options.
2. the 'van ..' options should go before those without 'van ..'. The EXIT FOR command should then prevent 'der' being used before 'van der'
Jonathan
Thx for your effort Jonathan, but Michael already got me on the right track.
See above for the solution I used.