How do you split data from one column into two?

Question

I just recently started learning about SQL in MS Access and SQL Server so I have very limited knowledge, but what I'm looking for is help with a query in MS Access.

I know how to merge 2 columns into 1 and have the final result separated by a comma or whatever symbol I'd like. But, how do I do the opposite?

In my case, I have a column (LastFirstName) in my table (MEMBERS) where the data would look something like this: "Smith, Middle John" etc.

What I'm having trouble with is figuring out how to permanently separate the data into 2 separate columns within the same table (LastName and FirstName) and not just using a query to display them like that.

Any help would be greatly appreciated, thanks!

That's for MySQL isn't it? I'm trying to do it in MS Access. — antonlab, May 30 '13 at 09:44

score 4 · Accepted Answer · answered May 30 '13 at 10:40

Starting with

memberID  LastFirstName  LastName  FirstName
--------  -------------  --------  ---------
       1  Doe, John

the query

UPDATE Members SET
    LastName = Trim(Left(LastFirstName, InStr(LastFirstName, ",") - 1)),
    FirstName = Trim(Mid(LastFirstName, InStr(LastFirstName, ",") + 1))

will result in

memberID  LastFirstName  LastName  FirstName
--------  -------------  --------  ---------
       1  Doe, John      Doe       John

How do you split data from one column into two?

1 Answers1

Linked