SQL - Converting from VARCHAR to Decimal

~J~ · 16 Jan 2006 at 15:16

I've got a table which has a field that can hold a multitude of values.

What I want to do is SUM up the field, if it can't convert the text, then it ignores it and/or gives it a zero.

I get an error which reads "Error converting varchar to numeric"

Anyone know how I can SUM up the column, ignoring any errors it finds along the way?

TIA

happytechie · 16 Jan 2006 at 15:24

which RDMBS are you using? The functions are different for all of them

Oracle: TO_NUMBER()
SQL Server: CONVERT()
and so on...

HT

~J~ · 16 Jan 2006 at 15:26

Ahhh, sorry, using SQL Server 2000.

Tried the Convert and Cast commands, but getting the same error.

Andre · 16 Jan 2006 at 16:14

something like

Code:

CASE WHEN [Field] LIKE '[0-9]+' THEN [Field] ELSE 0 END

as MSSQL does auto convert to numeric if required
totally untested, but the sort of thing I would probably do

Dr_Evil · 16 Jan 2006 at 16:16

use ISNUMERIC() to test if the varchar holds a numeric value before converting to an int using Cast or Convert.

~J~ · 16 Jan 2006 at 16:52

Cheers for the help, really appreciated.