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]

3 comments:

  1. nightmare stuff.

    ReplyDelete
  2. Thank you for the tips, Clay.

    ReplyDelete
  3. Thanks for the meal!! But yeah, thanks for spending the time to talk about this matter here on your web page.

    ReplyDelete