When i execute the following command I'm a little bit surprised of the result.

select -12345678901234.0001 as TestVal, str( TestVal, 40, 2) as stringVal
union all 
select -123456789012345.0001 as TestVal, str( TestVal, 40, 2) as stringVal
union all 
select -1234567890123456.0001 as TestVal, str( TestVal, 40, 2) as stringVal
union all 
select -12345678901234567.0001 as TestVal, str( TestVal, 40, 2) as stringVal
union all 
select -123456789012345678.0001 as TestVal, str( TestVal, 40, 2) as stringVal
union all 
select -1234567890123456789.0001 as TestVal, str( TestVal, 40, 2) as stringVal
union all 
select -12345678901234567890.0001 as TestVal, str( TestVal, 40, 2) as stringVal
union all 
select -123456789012345678901.0001 as TestVal, str( TestVal, 40, 2) as stringVal
union all 
select -1234567890123456789012.0001 as TestVal, str( TestVal, 40, 2) as stringVal
union all 
select -12345678901234567890123.0001 as TestVal, str( TestVal, 40, 2) as stringVal
union all 
select -123456789012345678901234.0001 as TestVal, str( TestVal, 40, 2) as stringVal
union all 
select -1234567890123456789012345.0001 as TestVal, str( TestVal, 40, 2) as stringVal
order by TestVal desc

The result is in iSQL

TestVal                         stringVal                                
------------------------------- -----------------------------------------
-12345678901234.0001                                  -12345678901234.00 
-123456789012345.0001                                -123456789012345.00 
-1234567890123456.0001                              -1234567890123456.00 
-12345678901234567.0001                            -12345678901234566.00 
-123456789012345678.0001                          -123456789012345680.00 
-1234567890123456789.0001                        -1234567890123456800.00 
-12345678901234567890.0001                      -12345678901234567000.00 
-123456789012345678901.0001                    -123456789012345680000.00 
-1234567890123456789012.0001                  -1234567890123456800000.00 
-12345678901234567890123.0001                -12345678901234568000000.00 
-123456789012345678901234.0001              -123456789012345690000000.00 
-1234567890123456789012345.0001            -1234567890123456800000000.00 
(12 rows)Execution time: 0.016 seconds

The Function STR is described as

Syntax 
STR( numeric-expression [, length [, decimal ] ] )

Parameters 
numeric-expression    Any approximate numeric (float, real, or double precision) expression between -1E126 and 1E127.
length    The number of characters to be returned (including the decimal point, all digits to the right and left of the decimal point, and blanks). The default is 10.
decimal    The number of decimal digits to be returned. The default is 0.

I think I did not overcome a length described as -1E127 I have tested this with SA 11.0.1.2355 and SA 10.0.1

Thanks for your help. Thomas

asked 05 Feb '10, 11:49

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.6k243561
accept rate: 17%


To clarify Mark's answer - STR() takes as its argument an approximate numeric argument (real or double), and not a numeric value of any type.

In your example the argument to STR() is numeric. The server (properly) parses the query and assigns the type to match the input, which in the case of the first SELECT is numeric(18,4). On evaluating STR(), the server is converting the numeric(18,4) value to float before converting it to a string, with the subsequent loss of precision.

select cast( -1234567890123456789012345.0001 as real) from sys.dummy

gives

-1.2345679468E24

and

select str( -1234567890123456789012345.0001,40,2) from sys.dummy

gives

-1234567890123456800000000.00

If you want to convert a numeric to a string, use CAST.

permanent link

answered 05 Feb '10, 13:19

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

Fully accepted. But the Documentation states 1E127. This is a much larger number isn't it ? I would expect to read numeric(18,4) in the documentation.

(05 Feb '10, 16:44) Thomas Dueme...
3

The first select uses numeric(18,4) because that is the precision and scale of the constant. The last SELECT in your UNION query is built as a numeric(29,4).

select exprtype( 'select -1234567890123456789012345.0001 as TestVal, str( TestVal, 40, 2) as stringVal from sys.dummy', 1)

gives

numeric(29,4)

The documentation states the maximum exponent that can be supported, but not the size of the mantissa. The double type is limited to 63 binary digits of precision (approximately 16 decimal digits) and that's it.

Again - if you want to convert numeric to string, use CAST.

(05 Feb '10, 17:48) Glenn Paulley

What you are seeing is the result of the evaluation of the string value as a double. The precision of a double is about 16 decimal digits - see http://en.wikipedia.org/wiki/Double_precision_floating-point_format - and as a result the 17th digit in your conversion of the double back to a string (i.e. STR() operator) will show you varying digits in the 17th decimal position depending on the binary respresentation of the number.

The 11.0.1 documentation talks about this here.

permanent link

answered 05 Feb '10, 13:10

Mark%20Culp's gravatar image

Mark Culp
23.2k9132273
accept rate: 40%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×137
×114
×35

question asked: 05 Feb '10, 11:49

question was seen: 1,465 times

last updated: 05 Feb '10, 13:19