如何使用派生列在 SSIS 中将字符串转换为小数? [英] How can I turn a string into a decimal in SSIS using a derived column?
问题描述
我在 SSIS 中工作以填充临时区域数据库.我想传输以下数据:
I am working in SSIS to fill a staging area database. I want to transfer the following data:
-------------------------------------------------------------------
| ShipName (String) | MaxContainers (int) | FuelCapacity (String) |
-------------------------------------------------------------------
| Ship1 | 1000 | 74.60 Liters |
| Ship2 | 1500 | 121.28 Liters |
-------------------------------------------------------------------
如您所见，FuelCapacity 列是一个包含数字和字母的字符串列.新数据库的布局如下:
As you can see the FuelCapacity column is a string column that contains numbers and letters. The new database has the following layout:
-------------------------------------------------------------------
| ShipName (String) | MaxContainers (int) | FuelCapacity (decimal)|
-------------------------------------------------------------------
| Ship1 | 1000 | 74.60 |
| Ship2 | 1500 | 121.28 |
-------------------------------------------------------------------
我怎样才能做到这一点.我相信我需要为此使用派生列，但我无法弄清楚如何.
How can I make this happen. I believe I need to use a derived column for this, but I cant see to figure out how.
PS:字符串列是数据库中的nvarchar数据类型
PS: the string columns are nvarchar data types in the database
推荐答案
再次说明问题，您需要根据第一个空格的位置拆分源列.我们需要将前半部分从空格中保留下来，然后将其转换为小数(或保留为字符串并使用 Gordon 的方法进行隐式转换).在这一点上对我来说无关紧要.
To restate the problem, you need to split the source column based on the location of the first space. We need to keep the first half from the space and then make that into a decimal (or leave as string and use Gordon's approach for implicit conversions). Makes no matter to me on that point.
当我看到这样的问题时，我想知道哪里会出现影响我设计的问题.
When I look at problems like this, I wonder where all things can go wrong which influences my design.
考虑到这一点，我将在我的数据流中添加 3 个派生列表达式来解决这个问题.
With that in mind, I am going to add 3 derived column expressions to my data flow to solve this.
我在我的包中添加了一个 OLE DB 源并使用以下查询
I added an OLE DB Source to my package and use the following query
SELECT '74.60 Liters' AS FuelCapacity
UNION ALL SELECT '121.28 Liters' AS FuelCapacity
UNION ALL SELECT '341.56 DeciLiters' AS FuelCapacity
UNION ALL SELECT '0.0 Liters'
UNION ALL SELECT NULL
最后 3 个未提供，但对于域来说似乎是合理的值.
The last 3 are not supplied but seem like reasonable values for the domain.
这个组件的目的是识别入站文本中的空格.我将向名为 SpaceOrdinal 的数据流添加一个新列，因为它将基于以下表达式
The purpose of this component is to identify the space in the inbound text. I will add a new column to the data flow called SpaceOrdinal as that will be based on the following expression
FINDSTRING([FuelCapacity]," ",1)
DER 获取第一个字符串
这个组件的目的是根据我们在上一步得到的序数位置切出我们想要的数据.我创建了一个名为 FirstString 的新列，因为它将包含第一段字符串数据.
DER Get First String
The purpose of this component is to slice out the data we want based on the ordinal position we obtained in the previous step. I created a new column called FirstString as it will contain the first piece of string data.
LEFT([FuelCapacity],[SpaceOrdinal])
Der 转换为十进制
这一步是可选的，因为您可以让隐式转换发生，也许您愿意/不希望它发生.名为 LitersOfFuel 的新列；
Der Convert to decimal
This step is optional as you can let implicit conversion happen and maybe you do/don't want it to. New column, called LitersOfFuel;
(DT_NUMERIC,10,2)FirstString
这适用于提供的数据.
但是如果 0.0 升只是 0 升呢?这里的转换会失败，因为转换非常脆弱.但是您可以添加一个前导步骤，将数据转换为使用默认类型转换效果更好的数据.
But what if the 0.0 Liters was just 0 Liters? The conversion here would fail as the conversion is quite brittle. But you can add a precursor step to transform the data into something that converts better with the default cast.
这篇关于如何使用派生列在 SSIS 中将字符串转换为小数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
