Replace Non-Numeric Values With Zero

Hi All,

I have a problem that I have no idea how to fix, not even sure if its possible but hopefully you can let me know.

So I am pulling data from one table to another table in a DTS. But I need to check if data in one of the columns contains a non-numeric value and if so change it to a 0. This is basically the code i'm using:

SELECT Product, Descript, Qty
FROM Products
ORDER BY Product, Qty, Descript

Does anyone know how to check for non-numeric values and perform this change ?

Rgds,
Paul

This might work,

SELECT Product, Descript, CASE WHEN ISNUMERIC(Qty) <> 0 THEN Qty ELSE 0 END AS Qty

FROM Products

ORDER BY Product, Qty, Descript

Anas.

|||

Just needed a slight change to the way you worded the Case statement but that is basically exactly what I needed.

Thanks a million Anas

|||

You might consider changing

ISNUMERIC(Qty)

with

ISNUMERIC(Qty+'E0')

Also, give this article a look

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

|||Thats very useful info .. thanks