我想使用 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());更多推荐
如何在 SSIS 中使用子字符串
发布评论