Split out and reverse a string

[dcavaiani]

“John and Mary Jones” s/b “Jones John”

“Jim & Bob & Mary Jones” s/b “Jones Jim”

 

[dcavaiani]

“John and Mary Jones” s/b “Jones John”

“Jim & Bob & Mary Jones” s/b “Jones Jim”

This is in a sql insert

 

[June7]

Right(x, InStr(x, " ")) & " " & Left(x, InStr(x, " "))

 

[arnelgp]

Mid([FieldName], InstrRev([FieldName], " ") + 1) & " " & Left([FieldName], Instr([FieldName], " ") - 1)

 

[plog]

James Van Der Beek
Oscar De La Hoya


You ok with those being these:

Beek James
Hoya Oscar

 

[dcavaiani]

Right(x, InStr(x, " ")) & " " & Left(x, InStr(x, " "))

I thought for sure I tried that and ran into a failure when testing? I will try again tomorrow.

 

[dcavaiani]

James Van Der Beek
Oscar De La Hoya


You ok with those being these:

Beek James
Hoya Oscar

I see that complicates it more ...

 

[dcavaiani]

I see that complicates it more ...

 

[June7]

Consistency in structure is critical with string manipulation. Names are far from being consistent in structure. This is why name parts should be saved into separate fields.

 

[dcavaiani]

Mid([FieldName], InstrRev([FieldName], " ") + 1) & " " & Left([FieldName], Instr([FieldName], " ") - 1)

This one did it !!