您如何将数据从一列拆分为两列? [英] How do you split data from one column into two?

问题描述

我最近才开始学习MS Access和SQL Server中的SQL，因此我的知识非常有限，但是我正在寻找的是有关MS Access中查询的帮助.

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.

我知道如何将2列合并为1，并用逗号或我想要的任何符号分隔最终结果.但是，我该如何做相反的事情?

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?

在我的情况下，我的表(MEMBERS)中有一列(LastFirstName)，数据看起来像这样:"Smith，Middle John"等.

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

我遇到的麻烦是弄清楚如何将数据永久地分成同一表(LastName和FirstName)中的2个单独的列，而不仅仅是使用查询来这样显示它们.

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!

推荐答案

以

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

查询

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

将导致

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

这篇关于您如何将数据从一列拆分为两列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！