mattturley
asked on
Selecting Part of a field
I swear I will get this down eventually!
I need to select only part of a User Name field. Some of the User names have a middle initial at the very end of the string that I need to remove.
I need to convert:
smith, john a to
smith, john
Thanks!
I need to select only part of a User Name field. Some of the User names have a middle initial at the very end of the string that I need to remove.
I need to convert:
smith, john a to
smith, john
Thanks!
Generic:
Mid(FullName,1,InStrRev(Fu llName"," "))
mx
Mid(FullName,1,InStrRev(Fu
mx
Opps .... extra ""
Mid([FullName],1,InStrRev( [FullName] ," "))
Query:
SELECT Mid([FullName],1,InStrRev( [FullName] ," ")) AS Expr1
FROM Table1;
mx
Mid([FullName],1,InStrRev(
Query:
SELECT Mid([FullName],1,InStrRev(
FROM Table1;
mx
ASKER
Not every record has a middle initial - when this is the case, the above gets rid of the first name.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As you can get what you want via a query, I recommend you leave your data intact. Run this query:
SELECT IIF(instr(mytest," ") = instrrev(mytest," "), mytest, Left(mytest,instr(instr(my test,", ")+2,mytest," "))) AS JustName FROM myTable;
SELECT IIF(instr(mytest," ") = instrrev(mytest," "), mytest, Left(mytest,instr(instr(my
Mid("smith, john a",1,InStrRev("smith, john a"," ")))
returns smith, john
mx