如何获得一个箱子大小的总重量，例如12 x 160g等于1.92kg

Question

不知道这是否可能。

但是，假设我在表中有一个像这样的列：

12 x 60g
12 x 160g
500g
1kg
1kg
12 x 160g
12 x 250g

有些是个人尺寸，有些是表壳尺寸。

是否可以获得每一行的总重量？如果是这样，将如何做？

例如

0.72kg  /* 12 x 0.060 = 0.72 */
1.92kg  /* 12 x 0.160 = 1.92 ... */
0.5kg
1kg
1kg
1.92kg
3kg

Answer 1

通过@Tomalak添加到注释中，下面是一个示例脚本，您可以使用该脚本来填充重量表达转换表。

CREATE TABLE dbo.Weights(
     Weight varchar(20) NOT NULL 
        CONSTRAINT PK_Weights PRIMARY KEY
    ,WeightInGrams int NULL
    ,WeightInKilograms AS CAST(WeightInGrams / 1000.0 AS decimal(10, 2))
);
GO

INSERT INTO dbo.Weights(Weight)
    SELECT DISTINCT Weight
    FROM dbo.Foo;
GO

DECLARE
      @Weight varchar(20)
    , @WeightInGrams int
    , @SqlExpression nvarchar(MAX);
DECLARE foo CURSOR LOCAL FAST_FORWARD FOR
    SELECT weight
    FROM dbo.Weights;
OPEN foo;
WHILE 1 = 1
BEGIN

    FETCH NEXT FROM foo INTO @Weight;

    IF @@FETCH_STATUS = -1 BREAK;

    BEGIN TRY

        SET @SqlExpression = 'SET @WeightInGrams = 
            ' + REPLACE(REPLACE(REPLACE(@Weight, 'x', '*'), 'kg', '000'), 'g', '');

        EXEC sp_executesql @SqlExpression
            , N'@WeightInGrams int OUTPUT'
            , @WeightInGrams = @WeightInGrams OUTPUT;

    END TRY
    BEGIN CATCH

        SET @WeightInGrams = NULL;

    END CATCH;

    UPDATE dbo.Weights
    SET WeightInGrams = @WeightInGrams
    WHERE Weight = @Weight;

END
CLOSE foo;
DEALLOCATE foo;
GO