getting sum of nvarchar field 1

[MikeT]

I use ASP to pull info from our SQL Server 2000 db and display it on the web.
One particular field is stored as nvarchar, even though it contains numbers (I inherited this beauty). I need to get the sum of this field, but you can't do it with nvarchars. I cant change the datatype for various reasons.
I can always get the field to add programmatically with vbscript, but it would very inefficient, and server intensive.

Am I SOL?

 

[tlbroadbent]

Select cast(CharCol As int) As NumCol

OR

Select cast(CharCol As decimal(8,2)) As NumCol

You may want to incorporate the IsNumeric function just to make sure the value can be converted and won't cause an error.

Select
Case When Isnumeric(CharCol)=1
Then cast(CharCol As decimal(8,2))
Else 0 End As NumCol
Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[MikeT]

Here is the sql statement:
SELECT cast(PurchasePrice As decimal(8,2)) As NumCol
SUM(NumCol) AS TotalProposedCurrent FROM PropertyInfo

It returns Syntax Error near SUM

PurchasePRice is the nvarchar column

Any idea?

 

[tlbroadbent]

Try this.

SELECT Sum(cast(PurchasePrice As decimal(8,2))) AS TotalProposedCurrent FROM PropertyInfo Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[MikeT]

That did it!

Thanks for you help, it is appreciated!