Stop the Hollyweb! No DRM in HTML5.   

Monday, May 24, 2010

Cast Scientific Notation as Money or Varchar

I run into Scientific Notation on occasion and always refer back to my TSQL scripts folder. Most of the time, I'll have a column where about 50% of the rows contain Scientific Notation. In these cases, I use a CASE statement.

First, CAST as VARCHAR:

LTRIM(RTRIM(CAST(CAST('2.50823E+12' AS FLOAT) AS NVARCHAR)))

Second, CAST as MONEY:

LTRIM(RTRIM(CAST(CAST(CAST('2.50823E+12' AS FLOAT) AS NVARCHAR)AS MONEY)))


Now, let's see this in a CASE statement:

,CASE
WHEN [AMOUNT] LIKE '%E-%' THEN LTRIM(RTRIM(CAST(CAST(CAST([AMOUNT]AS FLOAT) AS NVARCHAR)AS MONEY)))
WHEN [AMOUNT] = ' ' THEN NULL
ELSE [AMOUNT]
END AS [AMOUNT]