如何在派生列转换中替换双引号? [英] How to replace double quotes in derived column transformation?

问题描述

我有平面文件.我正在使用 ssis 将数据从平面文件加载到源表.其中一列具有以下值:

I have flat file. I am loading data from flat file to source table using ssis.And one of the column has following values:

<Somecol1 =""1"" col1values= ""223,567,890,653"">

我想要以下列输出:

<Somecol1 ="1" col1values= "223,567,890,653">

我尝试在派生列中进行替换.

I have tried to replace in derived column.

REPLACE( COLA, "\"","\")

但这不起作用.

推荐答案

我认为除了替换字符串中的附加斜杠外，您几乎已经正确地表达了.以下是可能适合您的表达方式.

I think you have almost got the expression correct except for the additional slash in the replacement string. Here are the expressions that might work for you.

表达式 #1: 删除给定字符串中的所有双引号.

Expression #1: Removes all double quotes within a given string.

REPLACE(COLA, "\"", "")

表达式 #2: 将所有 double 出现的双引号替换为 single 出现的双引号.

Expression #2: Replaces all double occurrences of double quotes with single occurrence of double quotes.

REPLACE(COLA, "\"\"", "\"")

这是一个演示表达式 #1 的示例:

Here is an example that demonstrates expression #1:

1. 屏幕截图 #1 显示了包将读取的 CSV 文件.
2. 屏幕截图 #2 显示了 数据流任务 内的 派生列转换，它将替换名为 <的第一列中的所有双引号强>标题.
3. 屏幕截图 #3 显示包执行后表中的数据.请注意，第二列中的双引号保持原样，因为没有表达式可以替换它们.

1. Screenshot #1 shows the CSV file that will be read by a package.
2. Screenshot #2 shows the Derived Column transformation inside the Data Flow task that will replace all double quotes within the first column named as Header.
3. Screenshot #3 shows data in the table after the package execution. Notice that the double quotes in second column are left as it is because there is no expression to replace them.

这是一个演示表达式 #2 的示例:

Here is an example that demonstrates expression #2:

1. 此示例将使用与示例 1 相同的文件.请参阅屏幕截图 #1.
2. 屏幕截图 #4 显示了 Data Flow 任务 内的 Derived Column 转换，它将替换所有 double 出现在名为 Header 的第一列中单个出现双引号的双引号.
3. 屏幕截图 #5 显示包执行后表中的数据.请注意，第二列中的双引号保持原样，因为没有表达式可以替换它们.

1. This example will use the same file as in example 1. Refer screenshot #1.
2. Screenshot #4 shows the Derived Column transformation inside the Data Flow task that will replace all double occurrences of double quotes with single occurrence of double quotes within the first column named as Header.
3. Screenshot #5 shows data in the table after the package execution. Notice that the double quotes in second column are left as it is because there is no expression to replace them.

希望有所帮助.

屏幕截图 #1:

屏幕截图 #2:

屏幕截图 #3:

屏幕截图 #4:

屏幕截图 #5:

这篇关于如何在派生列转换中替换双引号?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！