Cast Float to Char with Natural Rounding

• irobertson

  Hall of Fame

  Points: 3695

  More actions

  April 29, 2010 at 5:26 am

  #386816

  Hi,

  I have columns of float data type that I have to convert to char. With a natural cast, any big numbers get exponentialised:

  select cast(cast(12345667 as float) as nchar(20)) = 1.23457e+007

  I can convert using str which will give me a text output, but I have to specify length & scale:

  select cast(str(cast(12345667 as float),14,5) as nchar(20)) = 12345667.00000

  Which leaves me with redundant decimals.

  My data can have an indeterminate number of decimal places. Is it possible to naturally round the results so that they look like this:

  12345667

  12345667.9

  12345667.99

  12345667.999

  12345667.9999

  Instead of this:

  12345667.00000

  12345667.90000

  12345667.99000

  12345667.99900

  12345667.99990

  Without resorting to an inline function using something like:

  declare @input nchar(20)

  , @output nchar(20)

  , @conversion varchar(20)

  select @input = N'12345678.54640'

  select @conversion = cast(rtrim(@input) as varchar)

  select @conversion =

  left(@conversion,charindex('.',@conversion)) +

  reverse(

  cast(

  cast(

  reverse(right(@conversion,len(@conversion) - charindex('.',@conversion)))

  as int)

  as varchar)

  )

  select @output = cast(@conversion as nchar(20))

  select @output

  select @conversion

  Which seems like a recipe for slow running...

  Any advice appreciated.

  Thanks, Iain

• LutzM

  SSC Guru

  Points: 107049

  More actions

  April 29, 2010 at 5:48 am

  #1159575

  It's actually not a rounding issue. It's a form of presenting the result. This should be done at the frontend side, not within SQL Server.

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• Paul White

  SSC Guru

  Points: 150467

  More actions

  April 29, 2010 at 6:04 am

  #1159582

  USE tempdb;

  GO

  -- *Inline* table-valued function

  -- Deterministic, system verified, not precise

  GO

  CREATE FUNCTION dbo.NaturalRound

  (

  @Value DOUBLE PRECISION

  )

  RETURNS TABLE

  WITH SCHEMABINDING

  AS

  RETURN

  SELECT DV3.value

  FROM (SELECT @Value) V (value)

  CROSS

  APPLY (SELECT CONVERT(NVARCHAR(15), CONVERT(DECIMAL(14,5), value))) DV (value)

  CROSS

  APPLY (SELECT REVERSE(DV.value)) DV2 (reversed)

  CROSS

  APPLY (SELECT LEFT(DV.value, LEN(DV.value) + 1 - PATINDEX('%[^0.]%', DV2.reversed))) DV3 (value);

  GO

  -- Single value test

  SELECT NR.value

  FROM dbo.NaturalRound (1234.5678) NR

  GO

  -- Test with table

  DECLARE @data

  TABLE (

  value DOUBLE PRECISION NULL

  );

  INSERT @data

  VALUES (12345667),

  (12345667.9),

  (12345667.99),

  (12345667.999),

  (12345667.9999);

  SELECT D.value,

  NR.value

  FROM @data D

  CROSS

  APPLY dbo.NaturalRound (D.value) NR

  GO

  DROP FUNCTION dbo.NaturalRound;

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi

• irobertson

  Hall of Fame

  Points: 3695

  More actions

  April 29, 2010 at 7:38 am

  #1159676

  Hi Lutz,

  I'm migrating data between two quite different environments. It's my issue to handle because I've created the problem in the first place by converting from float.

  Paul, cast to double precision works perfectly, no complex functions required. Thanks.

  Regards, Iain

• Paul White

  SSC Guru

  Points: 150467

  More actions

  April 29, 2010 at 8:25 am

  #1159733

  irobertson (4/29/2010)

  ---

  Paul, cast to double precision works perfectly, no complex functions required.

  Hey Iain,

  Double precision is just a synonym of float.

  I thought my function was reasonably complex 😉 😀 :w00t:

  Sounds like you're happy anyway, which is the main thing.

  Paul

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi

• irobertson

  Hall of Fame

  Points: 3695

  More actions

  April 30, 2010 at 2:39 am

  #1160262

  Ha really? Lol, that won't work then will it? Complex function it is then.

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  April 30, 2010 at 8:30 pm

  #1160849

  irobertson (4/30/2010)

  ---

  Ha really? Lol, that won't work then will it? Complex function it is then.

  Do you know what the maximum number of decimal places will ever be for this data?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  April 30, 2010 at 8:53 pm

  #1160851

  Paul,

  Try this...

  -- Test with table

  DECLARE @data

  TABLE (

  value DOUBLE PRECISION NULL

  );

  INSERT @data

  VALUES (12345000),

  (12345000.9),

  (12345000.99),

  (12345000.999),

  (12345000.9999);

  SELECT D.value,

  NR.value

  FROM @data D

  CROSS

  APPLY dbo.NaturalRound (D.value) NR

  GO

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  April 30, 2010 at 10:05 pm

  #1160856

  Using the great start that Paul provided, the following will also drop the decimal point if a whole number is realized...

  USE tempdb;

  GO

  -- *Inline* table-Itemd function

  -- Deterministic, system verified, not precise

  GO

  CREATE FUNCTION dbo.NaturalRound

  (

  @Value DOUBLE PRECISION

  )

  RETURNS TABLE

  WITH SCHEMABINDING

  AS

  RETURN

  SELECT v.Value

  FROM (SELECT CONVERT(NVARCHAR(39), CONVERT(DECIMAL(38,8), @Value))) c (Converted)

  CROSS APPLY (SELECT REVERSE(c.Converted)) r (Reversed)

  CROSS APPLY (SELECT SUBSTRING(c.Converted,1,LEN(c.Converted)-PATINDEX('%[^0]%',r.Reversed)+1)) s (Stripped)

  CROSS APPLY (SELECT ISNULL(STUFF(s.Stripped,PATINDEX('%.',s.Stripped),1,''), s.Stripped)) v (Value)

  ;

  GO

  -- Single Item test

  SELECT nr.Value

  FROM dbo.NaturalRound (1234.5678) nr

  GO

  -- Test with table

  DECLARE @data

  TABLE (

  VALUE DOUBLE PRECISION NULL

  );

  INSERT @data

  SELECT (1234500) UNION ALL

  SELECT (1234500.) UNION ALL

  SELECT (1234500.0) UNION ALL

  SELECT (1234500.9) UNION ALL

  SELECT (1234500.99) UNION ALL

  SELECT (1234500.999) UNION ALL

  SELECT (1234500.9999);

  SELECT d.Value,

  nr.Value

  FROM @data d

  CROSS

  APPLY dbo.NaturalRound (d.Value) nr

  GO

  DROP FUNCTION dbo.NaturalRound;

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Paul White

  SSC Guru

  Points: 150467

  More actions

  May 1, 2010 at 12:29 am

  #1160873

  Jeff Moden (4/30/2010)

  ---

  Paul, try this...

  Ah. Yes, that is a bit of an issue isn't it? :rolleyes: 😀

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi

• Paul White

  SSC Guru

  Points: 150467

  More actions

  May 1, 2010 at 12:32 am

  #1160874

  Jeff Moden (4/30/2010)

  ---

  Using the great start that Paul provided, the following will also drop the decimal point if a whole number is realized...

  Great stuff! Thanks so much for taking the time to fix it. 🙂

  Nice APPLY work too :w00t:

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  May 1, 2010 at 8:43 am

  #1160899

  Paul White NZ (5/1/2010)

  ---

  Jeff Moden (4/30/2010)

  ---

  Using the great start that Paul provided, the following will also drop the decimal point if a whole number is realized...

  Great stuff! Thanks so much for taking the time to fix it. 🙂

  Nice APPLY work too :w00t:

  Heh... even an old dog like me can learn new tricks. In times past, I'd have written cascading CTEs. While those work, the CROSS APPLY derived table stuff sure makes the code pretty and easy to understand especially for things like this.

  Thanks for the clear, very well written articles on the subject, Paul.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)