parsing 1 column into 2

[terpster]

I have a column that contains both last name and first name seperated by a comma. I was wondering if there was an 'easy' way to parse that column to put it into two seperate columns.

thanks!

 

[rac2]

Depends on what you mean by easy.

SELECT [Applicants]![whole_name],
       InStr(1,[Applicants]![whole_name],",") AS [Position],
       Left([Applicants]![whole_name],[Position]-1) AS LastName,
       Mid([Applicants]![whole_name],[Position]+1,Len([Applicants]![whole_name])) AS FirstName
FROM Applicants;

or in normal SQL notation and simplified by assuming the name of the table

SELECT whole_name,
       InStr(1,whole_name,",") AS Position,
       Left(whole_name, Position-1) AS LastName,
       Mid(whole_name, Position]+1, Len(whole_name) ) AS FirstName
FROM Applicants;

Having done that, I would then add two columns to my table for first_name and last_name. Then I would use the functions in the above query to UPDATE the new columns with the first and last names. Then I would delete the whole_name column. In the future it would be really easy to get first and last name whichever I wanted, or put them together or to show them as last name, first name.

SELECT first_name FROM Applicants
SELECT last_name FROM Applicants
SELECT first_name + " " + last_name FROM Applicants
SELECT last_name + ", " + first_name FROM Applicants