sql server 2000, arithmetic operation on a varchar column 1

[pssheba]

Hi !
I imported a table from a text file and got a numeric column as a varchar.
I need to process arithmetic operations on the numbers of that column but i get an error message saying that arithmetic operations on a varchar column are impossible.
How can i transform a varchar digit into a numeric value ?
How can i change the whole column fro varchar into numeric ?
Thanks a lot !

 

[bborissov]

Convert varchar column to numeric one:
1. MAKE A GOOD BACKUP FIRST
2.:

ALTER TABLE MayTable ALTER COLUMN MyVarCharColumn Numeric(14,2)

BUT, this could give you error message IF ALL data in that column can't be convert to numeric. This means that if you have EMPTY field, field with 'asdasdasd' in it or any other non numeric expression that command will fails.

3.Other way:

ALTER TABLE MyTable ADD COLUMN MyNumericColumn Numeric (14,2)
UPDATE MyTable SET MyNumericColumn = CAST(MyVarCharColumn as numeric (14,2)) WHERE ISNUMERIC(MyVarCharColumn) = 1
[code]
That way you add an additiona column and convert all data from varchar column to numeric, but only if MyVarcharColumn contains expression that can be converted to numeric.

4. Directly use varchar for calculations:
[code]
SELECT CASE WHEN ISNUMERIC(MyVarCharColumn) = 0
            THEN 0 -- MyVarCharColumn contains non numeric expression
            ELSE CAST(MyVarCharColumn as numeric(14,2))
            END  * 10 / 732.99 ^1 AS TestMe -- :-)
...

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bborissov]

I forgot to say that expressions like

1e2
1d2

are valid numeric expressions

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[pssheba]

Thank you bborissov .
I thought your 3rd proposal would be the best but:
i wrote:

ALTER TABLE myTable 
ADD COLUMN
Amount Numeric,(14,2)
UPDATE myTable SET
Amount = CAST(Col001 as numeric (14,2))
WHERE
ISNUMERIC(Col002)=1

and it yielded the following error message:
"Incorrect syntax near the keyword 'COLUMN'."
any idea what went wrong ?
Thanks a lot !

 

[bborissov]

After Numeric you must have NO comma

Amount Numeric[COLOR=red],[/color](14,2)

must be

Amount Numeric (14,2)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[pssheba]

Noppp...
This is not the reason. The comma was left because i made all kinds of experiments...
This:

ALTER TABLE myTable
ADD COLUMN Amount Numeric (14,2)

is bringing me the same error message:
"Incorrect syntax near the keyword 'COLUMN'."
Do you see something wrong here ?
Thank a lot !

 

[bborissov]

My mistake, many frontends here

ALTER TABLE myTable ADD Amount Numeric (14,2)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[pssheba]

That worked fine and i'm having a new numeric column. (Thanks to you of course ..)
But this:

UPDATE myTable
SET
myNumeric = CAST (myVarChar as numeric (14,2))
WHERE ISNUMERIC (myvarchar) = 1

insists on telling me that:
"Error converting data type varchar to numeric".
This column contains figures such as: 4,000,000 (as varchar) or 4.11 and i think these are the values that stop the process. Is there a way to bypass it ?
Thanks a lot !

 

[bborissov]

The comma inside the varchar field causes the problem, so replace it with empty strung.

UPDATE myTable
SET
myNumeric = CAST(REPLACE(myVarChar, ',' , '') as numeric (14,2))
WHERE ISNUMERIC (myvarchar) = 1

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[pssheba]

It workes !
And you are a genius....

 

[bborissov]

If I am genius, just wait to get answers from gmmastros, SQLDenis and many others (I can't write them all here)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP