Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Hidden Formatting Troubles with STR() (SQL Spackle)

By Jeff Moden, (first published: 2010/12/15)

SQL Spackle" is a collection of short articles written based on multiple requests for similar code. These short articles are NOT meant to be complete solutions. Rather, they are meant to "fill in the cracks".

--Phil McCracken

Introduction

Most of the time, I try to teach what to do in T-SQL. This time, I'll teach what not to do. We all know that formatting data in SQL Server is your basic "Bozo-no-no" but we sometimes have to do some formatting to output to a file or for some other non-GUI related reason.

The STR() function appears to be mighty handy for formatting. It right justifies. It rounds to a given number of decimal points. It allows you to control the display width of an output column. What more can you ask for? If you use STR() for much more than simple integers or pre-rounded values , the answer might be, "A new job". Take a look...

The Hidden "Fault" with STR()

A lot of people never get into Books Online deep enough to realize that what they're using will make a huge but hidden "crack" in the foundation of their code. If you take a really good look at STR() in Books Online, you'll notice something and may still not understand the ramifications. Look carefully. Do you see it? Do you see the hidden "fault"?

Syntax
STR ( float_expression [ , length [ , decimal ] ] )

Still don't see it? Let me highlight it for you...

Syntax
STR ( float_expression [ , length [ , decimal ] ] )

"float_expression". What that means is that any numeric value that you try to format with STR() is first implicitly converted to the FLOAT data-type. What does that mean to you? Heh... it means huge "cracks" in your code if you're not careful. Let's see what that implicit conversion to FLOAT does to certain values formatted by STR().

Limited Number of Digits

Run the following code. The value in the very first SELECT is the maximum value for BIGINT. The value in the very last SELECT is, in fact, still much larger than an INTEGER. What will be the return?

 SELECT 9223372036854775807, STR(9223372036854775807,19) UNION ALL
 SELECT 922337203685477580,  STR(922337203685477580,19)  UNION ALL
 SELECT 92233720368547758,   STR(92233720368547758,19)   UNION ALL
 SELECT 9223372036854775,    STR(9223372036854775,19)    UNION ALL
 SELECT 922337203685477,     STR(922337203685477,19)

Here's the output:

--------------------------------------- -------------------
9223372036854775807                     9223372036854775800
922337203685477580                       922337203685477630
92233720368547758                         92233720368547760
9223372036854775                           9223372036854776
922337203685477                             922337203685477

Take a close look and see that, other than being right justified, the STR() conversion returned incorrect values for the right most column except for the last row. The last row, which is the first correctly converted row, has 15 digits which is also the limit for the FLOAT data-type. That's because STR() first converts the value to FLOAT.

The same goes for the right side of the decimal point. Run the following code and tell me what you see:

 SELECT .9223372036854775807, STR(.9223372036854775807,19,19) UNION ALL
 SELECT .922337203685477580,  STR(.922337203685477580,19,19)  UNION ALL
 SELECT .92233720368547758,   STR(.92233720368547758,19,19)   UNION ALL
 SELECT .9223372036854775,    STR(.9223372036854775,19,19)    UNION ALL
 SELECT .922337203685477,     STR(.922337203685477,19,19)


Here are the results:

--------------------------------------- -------------------
0.9223372036854775807                    0.9223372036854776
0.9223372036854775800                    0.9223372036854777
0.9223372036854775800                    0.9223372036854776
0.9223372036854775000                    0.9223372036854776
0.9223372036854770000                    0.9223372036854770


This time, we see 16 significant digits but the STR() conversion again returned incorrect values for the right-most column and we finally get lucky when we hit only 15 significant digits. I say we got "lucky" because that's not the end of the story...

Incorrect Rounding

It doesn't take many decimal digits to run into rounding errors with STR() formatting. Run the following code to see what I mean:

--===== Typical rounding problems same as FLOAT
WITH
cteGenerateExampleData AS
( --=== Build a million rows that start at 0.001 and end at 1000.000
     -- with an increment of 0.001. This only takes a couple of seconds. 
 SELECT TOP 1000000
        SomeNumber = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))*0.001
   FROM sys.all_columns ac1
  CROSS JOIN sys.all_columns ac2
)
,
cteConversions AS
( --=== Let's round the number using ROUND, a DECIMAL conversion, and STR()
 SELECT SomeNumber,
        SomeRound   = ROUND(SomeNumber,2),
        SomeDecimal = CAST(SomeNumber AS DECIMAL(10,2)),
        SomeSTR     = STR(SomeNumber,10,2)
   FROM cteGenerateExampleData
) --=== Finally, show where the Decimal and STR() formatted numbers differ
     -- from the correctly rounded value.
 SELECT SomeNumber,
        SomeRound,
        SomeDecimal,
        SomeSTR,
        ErrorReason = ISNULL(CASE WHEN SomeDecimal <> SomeRound THEN 'Bad Decimal Rounding ' END,'')
                    + ISNULL(CASE WHEN SomeStr     <> SomeRound THEN 'Bad STR Rounding '     END,'')
   FROM cteConversions
  WHERE SomeDecimal <> SomeRound
     OR SomeStr     <> SomeRound
;

I don't want to list out all 40,507 rows but here are the first 10:

SomeNumber SomeRound SomeDecimal SomeSTR ErrorReason
0.015      0.020     0.02           0.01 Bad STR Rounding
0.045      0.050     0.05           0.04 Bad STR Rounding 
0.075      0.080     0.08           0.07 Bad STR Rounding 
0.145      0.150     0.15           0.14 Bad STR Rounding 
0.175      0.180     0.18           0.17 Bad STR Rounding 
0.205      0.210     0.21           0.20 Bad STR Rounding 
0.235      0.240     0.24           0.23 Bad STR Rounding 
0.285      0.290     0.29           0.28 Bad STR Rounding 
0.295      0.300     0.30           0.29 Bad STR Rounding 
0.305      0.310     0.31           0.30 Bad STR Rounding

As you can see, the STR() formatted number doesn't always round correctly. That's because the underlying FLOAT conversion for numbers like 5.145 is actually 5.144999999999999 and that's the cause of the incorrect rounding from the 3rd decimal place. STR() sees the number as 5.144 and rounds it to 5.14 instead of 5.145

If you really need the simplified way that STR() does right justification, here are a couple of rules you should follow...

  1. Don't use more than 15 digits... ever.
  2. Don't use the rounding of STR(). Force the correct rounding using ROUND or a proper conversion to DECIMAL. Then use STR() to do the right justification.

Now... before you get all excited about using STR() just because you know the rules, we're still not done...

STR() is... SLOW!

Don't take my word for it though. Run your own test and prove it to yourself with the following code.

--===== Conditionally drop and rebuild a test table in a nice safe place
     -- that everyone has.  This only takes several seconds.
     IF OBJECT_ID('tempdb..#MyHead','U') IS NOT NULL
        DROP TABLE #MyHead
;
 SELECT TOP 1000000
        SomeNumber = IDENTITY(INT,1,1)
   INTO #MyHead
   FROM sys.all_columns ac1
  CROSS JOIN sys.all_columns ac2
;
--===== Declare a variable which is used to take display time out of the picture
DECLARE @BitBucket NVARCHAR(10)
;
--===== Identify the test and enable the timer for display
  PRINT '========== Cast, Concatenate, and Size ==========';
    SET STATISTICS TIME ON
;
--===== Slightly more complex method for right justification is more than 
     -- twice as fast as STR() formatting
 SELECT @BitBucket = RIGHT('0000000000' + CAST(SomeNumber AS VARCHAR(10)),10)
   FROM #MyHead
;
    SET STATISTICS TIME OFF
;
--===== Identify the test and enable the timer for display
  PRINT '========== The STR() Method is SLOWER ==========';
    SET STATISTICS TIME ON
;
 SELECT @BitBucket = STR(SomeNumber,10)
   FROM #MyHead
;
--===== Turn the "clock" off
    SET STATISTICS TIME OFF
;

The results from that bit of computational heaven are as follows. Of course, your times are likely going to be a whole lot faster than what I got from my 8 year old desktop box.

(1000000 row(s) affected)
========== Cast, Concatenate, and Size ==========

SQL Server Execution Times:
   CPU time = 1516 ms,  elapsed time = 1540 ms.
========== The STR() Method is SLOWER ==========

SQL Server Execution Times:
   CPU time = 3890 ms,  elapsed time = 3995 ms.


Crack filled!

Thanks for listening folks.

--Jeff Moden

Total article views: 10988 | Views in the last 30 days: 5
 
Related Articles
FORUM

rounding of decimals

rounding of decimals

FORUM

Currency Format

Cut out Decimal for Currency format without rounding up

FORUM

E-format float update performance

E-format float update performance

FORUM

Select Decimal

Need Decimal() function

FORUM

Remove Decimals Without Rounding

How do I remove decimals

Tags
sql spackle    
string manipulation    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones