在SSIS中使用右功能在列中拆分值 [英] Split a Value in a Column with Right Function in SSIS

问题描述

我需要你们的紧急帮助，我有一列代表用户全名的东西，现在我想将其拆分为名字和姓氏。

I need an urgent help from you guys, the thing i have a column which represent the full name of a user , now i want to split it into first and last name.

全名的格式为世界，你好，现在这里的名字为 hello，姓氏为世界。

The format of the Full name is "World, hello", now the first name here is hello and last name is world.

我正在使用派生列（SSIS），并使用Right Function的名字和substring函数的名字，但是这些结果似乎是空白的，即使我是空白。 ：）

I am using Derived Column(SSIS) and using Right Function for First Name and substring function for last name, but the result of these seems to be blank, this where even i am blank. :)

推荐答案

它对我有用。通常，您应该在类似此类地方的问题中提供更多详细信息，以帮助其他人重新创建问题并解决问题。您没有指定我们是否需要在此字段中处理NULL，也不知道您想如何解释它，以便在此答案上有改进的余地。

It's working for me. In general, you should provide more detail in your questions on places such as this to help others recreate and troubleshoot your issue. You did not specify whether we needed to address NULLs in this field nor do I know how you'd want to interpret it so there is room for improvement on this answer.

我从一个简单的OLE DB源开始，然后对 SELECT'World，Hello'AS名称的查询进行硬编码。

I started with a simple OLE DB Source and hard coded a query of "SELECT 'World, Hello' AS Name".

我创建了2个派生列任务。第一个在Data Flow中添加一列，称为FirstCommaPosition。我使用的公式是 FINDSTRING（Name，，，1）如果NAME可为空，则在调用FINDSTRING函数之前，我们需要测试可为空性。然后，您需要确定在使用NULL的情况下如何存储拆分数据。我认为第一个和最后一个都应该为NULL，但我不知道。

I created 2 Derived Column Tasks. The first one adds a column to Data Flow called FirstCommaPosition. The formula I used is FINDSTRING(Name,",", 1) If NAME is NULLable, then we will need to test for nullability prior to calling the FINDSTRING function. You'll then need to determine how you will want to store the split data in the case of NULLs. I would assume both first and last are should be NULLed but I don't know that.

有两个理由分别进行此操作脚步。首先是性能。听起来不合常理，因为SSIS引擎可以更好地并行化操作，所以在派生列中执行较少操作会导致更好的性能。另一个更简单-我将需要使用该值来分割名字和姓氏，因此与复制粘贴公式相比，引用列将更容易且维护更少。

There are two reasons for doing this in separate steps. The first is performance. As counter-intuitive as it sounds, doing less in a derived column results in better performance because the SSIS engine can better parallelize the operations. The other is more simple - I will need to use this value to make the first and last name split so it will be easier and less maintenance to reference a column than to copy paste a formula.

第二个派生列将实际执行拆分。

The second Derived Column is going to actually perform the split.

我的FirstNameUnicode列使用此公式（FirstCommaPosition> 0）？ RTRIM（LTRIM（RIGHT（Name，FirstCommaPosition）））： 这表示如果在上一步中找到了逗号，则将逗号位置到字符串末尾的所有内容都切掉，然后应用修剪操作。如果找不到逗号，则返回一个空白字符串。表达式的默认字符串类型为Unicode（DT_WSTR），因此如果不需要，则需要将结果转换为正确的字符串代码页（DT_STR）

My FirstNameUnicode column uses this formula (FirstCommaPosition > 0) ? RTRIM(LTRIM(RIGHT(Name,FirstCommaPosition))) : "" That says "If we found a comma in the preceding step, then slice out everything from the comma's position to the end of the string and apply trim operations. If we didn't find a comma, then just return a blank string. The default string type for expressions will be the Unicode (DT_WSTR) so if that is not your need, you will need to cast the resultant into the correct string codepage (DT_STR)

我的LastNameUnicode列使用以下公式（FirstCommaPosition> 0）？SUBSTRING（Name，1，FirstCommaPosition -1） ： 与上面类似的逻辑，只是现在我使用SUBSTRING操作而不是RIGHT。2012版SSIS及更高版本的用户，很高兴您可以使用LEFT函数代替SUBSTRING。您将需要退回1个位置以删除逗号。

My LastNameUnicode column uses this formula (FirstCommaPosition > 0) ? SUBSTRING(Name,1,FirstCommaPosition -1) : "" Similar logic as above except now I use the SUBSTRING operation instead of RIGHT. Users of the 2012 release of SSIS and beyond, rejoice fo you can use the LEFT function instead of SUBSTRING. Also note that you will need to back off 1 position to remove the comma.

这篇关于在SSIS中使用右功能在列中拆分值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！