how to do muliplication within UPDATE ?

Hi

I have a product table containing price. I want to update every price in a table by 2.5.

Unfortunately, the price field is actually a varchar(20) and I am not allowed to change it into int because this is a legacy program and the project leads forbid to make change to the database

I did a user-defined scalar function to convert string into int and try the following update statement but all it does is to change 0.0 into 0 .

UPDATE Product_Info SET Price = str(dbo.cval(Price)*2)

Any idea please?

***Before***

9> select price from product_info
10> go
price
--
0.00
0.00
0.00
0.00
1
0.00
0.00
9

**1_pri
**2_pri
**3_pri

*** After ****

1> select price from product_info
2> go
price
--
0
0
0
0
1
0
0
9

0
0
0

The function:

-
User-Defined Scalar Function
-

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: 7 Nov 2006
-- Description: Convert String into Int
-- =============================================
CREATE FUNCTION [dbo].[CVal]
(
-- Add the parameters for the function here
@.input varchar(10)
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
--DECLARE @.val VARCHAR(12)
--DECLARE @.return INT
--SET @.val = @.input

--SET @.return = SELECT CONVERT(INT, LEFT(@.val,PATINDEX('%[^0-9]%',@.val+' ')-1))

RETURN CONVERT(INT, LEFT(@.input,PATINDEX('%[^0-9]%',@.input+' ')-1))

END

incerese by 2.5 or 2.5 times !!!

update Product_Info set price = (cast(price as float) + 2.5)

or if 2.5 times id wat u want

update Product_Info set price = (cast(price as float) * 2.5)

|||

wow....that is amazing

that works perfectly

Thank you