Split configuration names

[fuecheefang]

I have some configuration names that I need to split:



example:

Dervisevic A. [C] need to split into two columns Last Name and First Initial

Last Name	First Initial
Dervisevic	A. [C]

Also the data is pretty bad, I also have some data in this manner as well that needs to be separated too (there is a period in between last name and first inital:
O'Brien.N NEW [C]

Last Name	First Initial
O'Brien	N. NEW [C]

[CJ_London]

If these are your variations I would replace all . with spaces, then insert a . for the initial and trim for superfluous spaces. Something like

LastName:trim(left(replace(myname,"."," "), instr(replace(myname,"."," ")," ")-1))
FirstInitial:replace(trim(mid(myname,len(trim(left (replace(myname,"."," "), instr(replace(myname,"."," ")," ")-1))))," ",".",1,1)

[fuecheefang]

If these are your variations I would replace all . with spaces, then insert a . for the initial and trim for superfluous spaces. Something like

LastName:trim(left(replace(myname,"."," "), instr(replace(myname,"."," ")," ")-1))
FirstInitial:replace(trim(mid(myname,len(trim(left (replace(myname,"."," "), instr(replace(myname,"."," ")," ")-1))))," ",".",1,1)

Thanks Ajax I'm still pretty new, can you clarify by providing the full coding? Would it be something like this?

SELECT OSX.[Configuration Name]

trim(left(replace(OSX.[Configuration Name],"."," "), instr(replace(OSX.[Configuration Name],"."," ")," ")-1)) as Last Name

replace(trim(mid(OSX.[Configuration Name],len(trim(left (replace(OSX.[Configuration Name],"."," "), instr(replace(OSX.[Configuration Name],"."," ")," ")-1))))," ",".",1,1) as First Name

FROM OSX;

[CJ_London]

looks like it. I didn't test the code

[fuecheefang]

looks like it. I didn't test the code

thanks Ajax, So close. I need the First initial to cut off the first 2 characters the z. in (z.C.(AP) [C])

Configuration Name	LastName	FirstInitial
Dietz C. (AP) [C]	Dietz	z.C. (AP) [C]

[fuecheefang]

thanks Ajax, So close. I need the First initial to cut off the first 2 characters the z. in (z.C.(AP) [C])

Configuration Name	LastName	FirstInitial
Dietz C. (AP) [C]	Dietz	z.C. (AP) [C]

*****Problem Solved***** Ajax you are awesome!

[CJ_London]

this should be basic stuff for you

modify to

replace(trim(mid(OSX.[Configuration Name],len(trim(left (replace(OSX.[Configuration Name],"."," "), instr(replace(OSX.[Configuration Name],"."," ")," ")-1))+2))," ",".",1,1) as First Name

think I have it in the right place