(It's a good thing I remember to copy my reply to the clipboard before posting it - I ran once more in the issue [probably timeout related, I always get it after spending a lot of time on a reply] where I get an error and lose my reply, but now I could just refresh the site and paste my answer!)
L' Eomot Inversé (10/18/2012)
But the explanation is very poor, inaccurate. For example the length of the expression "123.45" is 6, but str(123.45,4,2) is not '**' as suggested by the explanation but '123'.
And unfortunately, the explanation in Books Online is only marginally better. :angry: That goes for both the SQL Server 2008 version (linked in the question's explanation) and the SQL Server 2012 version (http://msdn.microsoft.com/en-us/library/ms189527.aspx). They are even partially incorrect!!!
Based on some tests I ran, this is how STR apparently works:
* The first argument (value) is mandatory. It can be any data type, but a conversion error will be raised if it can't be converted to float (!!).
* The second argument (length) is optional. If omitted, 10 will be assumed. If length is not an integer, an error will be raised. If the length is less than 1 or more than 8000, NULL will be returned. Otherwise, a varchar string of the specified length will be returned.
* The third argument (decimal) is optional. If omitted, 10 will be assumed. It is not possible to specify decimal without specifying length, but the reverse is possible. If decimal is not an integer, an error is raised. If decimal is less then 0, NULL will be returned. Otherwise, the requested precision will be determined as the minimum of decimal and 16.
1. The length of the integer fraction is determined. This is equal to the number of digits in value before the decimal seperator, plus one if value is negative. If the length of the integer fraction is less then length, the returned value will be a string of length '*' characters.
2. The maximum available precision is determined. This is equal to length - (the length of the integer fraction + 1). If that calculation returns -1, the maximum available precision is set to 0.
3. The actual precision is now determined as the lowest of the requested precision and the maximum available precision.
4. If the actual precision is 16, value is truncated to that number of decimal places1. If the actual precision is between 0 and 15, vallue is rounded to that number of decimal places.
5. The rounded or truncated value is now converted to string. When needed, trailing zeroes are added to get the number of positions after the deimal seperator equal to the actual precision. The formatted value is then right-adjusted - i.e., leading spaces are added to ensure that the returned string uses all available positions2.
1 This is documented incorrectly in Books Online. The documentation suggests that truncation will be used if the specified decimal is more than 16, but truncation is actually also used when it is equal to 16.
2 Since the value returned will always use the maximum available length of the returned string, it would probably make more sense to return a fixed-length char(length) value. However, I doublechecked, and the value returned is actually varchar(length).