如何在 SSIS 中使用子字符串

本文介绍了如何在 SSIS 中使用子字符串的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想使用 SSIS 将数据从 SharePoint 列表导出到 SQL.在 SharePoint 列表中，我有一列作为多选，所以我的列中的值低于

I want to export data from SharePoint list to SQL using SSIS. In SharePoint list, i have a column as multi select, So i am getting below value in my column

1;#control 1;#3;#control 3

我想在派生列中使用子字符串，这样我应该得到低于结果

I want to use substring in derived column in such a way that i should get below result

1,3

我只想要给定列中的 ID.

I want only ID from the given column.

我试过下面的代码

SUBSTRING(ColumnName,1,FINDSTRING(ColumnName,";#",1) - 1)

SUBSTRING(ColumnName,1,FINDSTRING(ColumnName,";#",1) - 1)

但它只给我答案

1

谁能帮帮我.

推荐答案

因为在您的 SharePoint Multi-Select 中选择了未知数量的控件，所以派生列转换对您不起作用.您必须使用脚本.

Because there is an unknown number of controls selected in your SharePoint Multi-Select, a Derived Column transformation is not going to work for you. You'll have to use a script.

解析字符串的一种方法是使用正则表达式.您必须向脚本转换添加一个输出，并将您解析的字符串分配给该输出.

One way to parse your string is with regular expressions. You'll have to add an output to the script transformation and assign your parsed string to that output.

Regex controlExpression = new Regex(@"control ([0-9]+)"); MatchCollection controlMatches = controlExpression.Matches(--YOUR INPUT HERE--); String output = string.Join(",", (controlMatches.Cast<Match>().Select(n => n.Groups[1].ToString())).ToArray());