SSIS 转换——将一列拆分为多列 [英] SSIS Transform -- Split one column into multiple columns

问题描述

我正在尝试找出如何在将结果导出到 CSV 文件后拆分表格中的一列并将其拆分为三列.

I'm trying to find out how to split a column I have in a table and split it into three columns after the result is exported to a CSV file.

例如，我有一个名为 fullpatientname 的字段.它以以下文本格式列出:

For example, I have a field called fullpatientname. It is listed in the following text format:

Smith, John C

期望将它放在三个单独的列中:

The expectation is to have it in three separate columns:

Smith
John
C

我很确定我必须将其拆分到派生列中，但我不确定如何处理

I'm quite sure I have to split this in a derived column, but I'm not sure how to proceed with that

推荐答案

您将需要在此过程中使用派生列.

You are going to need to use a derived column for this process.

SUBSTRING 和 FINDSTRING 函数将是实现这一目标的关键.

The SUBSTRING and FINDSTRING functions will be key to pull this off.

要获得第一个片段，您可以使用以下方法:

To get the first segment you would use something like this:

(DT_STR,25,1252) SUBSTRING([fullpatientname], 1, FINDSTRING(",",[fullpatientname],1)-1)

(DT_STR,25,1252) SUBSTRING([fullpatientname], 1, FINDSTRING(",",[fullpatientname],1)-1)

上面应该显示一个从 [fullpatientname] 开头到逗号 (,) 之前位置的子字符串.

The above should display a substring starting with the beginning of the [fullpatientname] to the position prior to the comma (,).

下一段是从逗号后面的位置到最后一个空格分隔符，最后是从最后一个空格分隔符后面的位置到结尾的所有内容.

The next segment would be from the position after the comma to the final space separator, and the final would be everything from the position following the final space separator to the end.

这篇关于SSIS 转换——将一列拆分为多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！