Friday, June 1, 2007

Casting to NUMERIC/DECIMAL with E notation

Found this neat tidbit on some other guy's blog. Basically, NUMERICs/DECIMALs are exact numeric types and cannot contain the E notation in their representations. FLOATs are approximate numeric types and can contain the E notation. FLOATs can also be CASTed to NUMERIC/DECIMAL with no problems (other than a loss of precision).

For example, take the VARCHAR value X = '10000000E-2'. To cast it to NUMERIC/DECIMAL, use the following:

SELECT CAST(CAST(X AS FLOAT) AS NUMERIC(15, 4))...

You would obviously lose some precision if you cast with a NUMERIC field of less precision than what is represented by the contents of the VARCHAR value.

No comments: