如何在 SSIS 中使用子字符串 [英] How to use Substring in 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());

这篇关于如何在 SSIS 中使用子字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！