Data Multiplication inside a SQL Server stored procedure

SQL Server 2008 : I have a table with three columns Ship Qty, Size, Qty.

We need to update the column Qty by multiplying two other columns.

At present this is the data in the table

Ship Qty            Size              Qty
2                   1*100  EA         NULL
3              开发者_开发技巧     3*20 ML           

Expected result after query

Ship Qty            Size              Qty
2                   1*100  EA         200
3                   3*20 ML           180

1. Split Size to Size1 (Before X) and Size2 (Behind X) (e.g 1X100 EA => Size1 = 1, SIze2=100)
2. Multiply Ship Qty * Size1 * Size2
3. Update Field qty with the result where qty is null or empty

I have no idea, how can I do it inside SQL Server? I am sure a stored procedure would be helpful !

---

Here is one try. It makes a lot of assumptions, e.g. that no space will occur until AFTER Size2 and that both sides of the * (or X from your description - which is it?) will be convertible to INT.

    DECLARE @foo TABLE
    (
        [Ship Qty] INT,
        Size VARCHAR(32),
        Qty INT
    );

    INSERT @foo([Ship Qty], Size)
    SELECT 2, '1*100 EA'
    UNION SELECT 3, '3*20 ML';

    UPDATE @foo SET
        Qty = [Ship Qty] * CONVERT(INT, LEFT(Size, CHARINDEX('*', Size)-1)) * 
        CONVERT(INT, SUBSTRING(Size, CHARINDEX('*', Size)+1, 
            CHARINDEX(' ', Size)-CHARINDEX('*', Size)))
    WHERE
        CHARINDEX('*', Size) > 0
        AND CHARINDEX(' ', Size) > 0
        AND Qty IS NULL;

    SELECT [Ship Qty], Size, Qty FROM @foo;

I strongly recommend storing this data better. Instead of storing "1*100 EA" store 1 in a column, 100 in a column, and EA in a column. This is going to be a nightmare to maintain, never mind to enforce that the size column gets consistent data (and that your formula will work against all future implementations).

I also suggest not having column names with special characters (e.g. spaces). Nobody likes having to type square brackets when they can be avoided.

Finally, if you can enforce the data on input, you should consider a computed column or view. You shouldn't have to constantly go back and update the table after the fact.

---

If the format is fixed, you could use functions like patindex and substring to parse the size:

select  [Ship Qty]
,       Size
,       [Ship Qty] * Size1 * Size2
from    (
        select  cast(substring(Size, 1, StarPos-1) as int) as Size1
        ,       cast(substring(Size, StarPos+1, SpacePos-StarPos-1) as int) 
                    as Size2
        ,       *
        from    (
                select  patindex('%*%', Size) as StarPos
                ,       patindex('% %', Size) as SpacePos
                ,       *
                from    @t
                ) as SubQueryAlias1
        ) as SubQueryAlias2

Here's a full example at ODATA.

---

You may try PARSENAME

-- create simple data, copy from Aaron Bertrand
DECLARE @foo TABLE
(
    [Ship Qty] INT,
    Size VARCHAR(32),
    Qty INT
);

INSERT @foo([Ship Qty], Size)
SELECT 2, '1*100 EA'
UNION SELECT 3, '3*20 ML'
UNION SELECT 3, '1*4.7 ML';

-- calcuate Qty    
WITH f AS
(SELECT  [Ship Qty],'[' + REPLACE(REPLACE(Size,'*','].['),' ','].[')+']' as size,Qty
 FROM @foo
 )
SELECT  [Ship Qty],PARSENAME(size,3) as Size1,PARSENAME(size,2) as Size2,
     1.0*[Ship Qty] * PARSENAME(size,3) * PARSENAME(size,2) AS Qty
FROM    f

---

DECLARE @foo TABLE
( Id int,
[Ship Qty] INT, Size VARCHAR(32), Qty Float
);

INSERT @foo( Id, [Ship Qty], Size)

select Id,[Ship Qty], [Size] from PlacedOrderDetails where Qty is Null ;
UPDATE @foo SET Qty = [Ship Qty] * CONVERT(INT, LEFT(Size, CHARINDEX('X', Size)-1)) * CONVERT(Float, SUBSTRING(Size, CHARINDEX('X', Size)+1, CHARINDEX(' ', Size)-CHARINDEX('X', Size)))
WHERE CHARINDEX('X', Size) > 0 AND CHARINDEX(' ', Size) > 0 AND Qty IS NULL;

SELECT Id,[Ship Qty],Size, Qty FROM @foo;

-- ***** -- This query results great -- Issue is that, It is inserting into Temp Table -- I want to update it instantly on the Parent Table : PlacedOrderDetails

---

I'm surprised nobody suggested computed column. I do agree with Aaron re normalising data structure. One column should not have 2 dimensions and a type. This should be 3 columns. This should not be allowed in database.

As for computed column - this will save on triggers. In SQL Server, go into table, modify column QTY and set "Computed Column Specification" to desired function. You can check more about computed columns here: http://msdn.microsoft.com/en-us/library/ms191250.aspx