getting sum from varchar field 2

[mplbm]

I have a varchar field that may be null that I need to get the value from and get the sum. I need to convert the value to a decimal in order to do this, but I keep getting the error 'Error converting data type varchar to numeric'.

SELECT isnull(sum(cast(isnull(ProductFields.Mobile_GPV,0) as decimal)),0)
FROM ProductFields

Any ideas what I am doing wrong here?


Cheers,

Mike

 

[vongrunt]

Some values in varchar column do not contain numeric data.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

to find out the non numeric values do this


SELECT * FROM ProductFields
WHERE Mobile_GPV NOT LIKE '%[a-z]%'
AND ISNUMERIC(Mobile_GPV) = 1


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ks2000]

To find out the non numeric values, The below query would be fine

SELECT * FROM ProductFields
WHERE ISNUMERIC(Mobile_GPV) = 0



Thanks

 

[SQLDenis]

I actually gave the code to check for if the values are numeric

you can not just use isnumer(field) =0 since
SELECT ISNUMERIC('1d2') returns 1 (there are more example)

so here we go

CREATE TABLE #foo (Value VARCHAR(20))
INSERT INTO #foo
SELECT '1' UNION ALL
SELECT '3' UNION ALL
SELECT 'B' UNION ALL
SELECT '2' UNION ALL
SELECT '33.331' UNION ALL
SELECT 'adad1' UNION ALL
SELECT '1d2' UNION ALL
SELECT '^' UNION ALL
SELECT '17777.999'


-- incorrect
SELECT * FROM #foo
WHERE ISNUMERIC(Value) = 1

-- incorrect
SELECT * FROM #foo
WHERE Value  LIKE '%[0-9]%'

--Maybe correct
SELECT * FROM #foo
WHERE Value  LIKE '%[a-z]%'
OR Value NOT LIKE '%[0-9]%'
OR ISNUMERIC(Value) =0

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mplbm]

Cheers!