I am getting varchar to numeric error?

What I am I doing wrong? Is there a better way of doing this, breaking out length and with from a string and converting 开发者_如何学编程it to an numeric value?

UPDATE VF_CasINV_Cost
   SET TotWgt = CASE
        WHEN (Dimension like '%x%') THEN
        (cast(left(Dimension, charindex('X', Dimension, 1) - 1) as numeric))  *
        (cast(substring(Dimension, 1 + charindex('X', Dimension, 1), len(Dimension)) as numeric)) / 144.00

        WHEN TotWgt = 0 and [Wt/Ft]  > 0 THEN  (Dimension * [Wt/Ft]) * [Count]
        WHEN TotWgt = 0 and [Wt/Sht] > 0 THEN ((Dimension * [Wt/Sht]) / 100) * [Count]
        WHEN TotWgt = 0 and [Wt/Pc]  > 0 THEN ((Dimension * [Wt/Pc] ) / 100) * [Count]
        ELSE TotWgt
    END --TotWgt, Item#, TotWgt, [Wt/Ft], [Wt/Sht], [Wt/Pc], [Count], Dimension, TotWgt
FROM VF_CasINV_Cost
WHERE (CalendarYear = 2010) AND (Item# = Item#) AND (ID = ID) AND (Item# < 99999990)
AND K = 'K' AND D = '' AND C = '' AND (Unit = 'cwt' OR Unit = 'cwts')

---

I'll make some assumptions: Dimension is a varchar field; you are trying to parse out the numeric value out of this varchar field and use it in calculations. If so, your problem is last three WHEN cases: you are using Dimension in multiplication operations without casting it. so SQL can't perform multiplication on a varchar field.