March 15, 2014 at 5:29 am
Stumbled on something strange & pecular when implementing a query using TRY_PARSE.
Started to have a closer look at things, and it boils down to this:
A query anyone can try to see for themselves:
;
WITH ListValues AS ( SELECT '1,234.56' AS StringValue UNION ALL SELECT '1234.56' )
SELECTStringValue
,TRY_PARSE( StringValue AS NUMERIC(11,2) )NUMERIC
,TRY_PARSE( StringValue AS DECIMAL(11,2) )DECIMAL
,TRY_PARSE( StringValue AS FLOAT(25) )FLOAT
FROMListValues
This results as follows:
StringValue NUMERIC DECIMAL FLOAT
----------- -------- -------- ------
1,234.56 1234.56 1234.56 NULL
1234.56 1234.56 1234.56 1234.56
TRY_PARSE to NUMERIC goes well
TRY_PARSE to DECIMAL goes well
TRY_PARSE to FLOAT won't do
Now, that's seems a bug to me 🙂
"Walking on water and developing software from a specification are easy if both are frozen." -- Edward V. Berard, "Life-Cycle Approaches"
March 17, 2014 at 9:20 am
Not really a bug, just .net doing what it says it will
The values for the data_type parameter are restricted to the types shown in the following table, together with styles. The style information is provided to help determine what types of patterns are allowed. For more information on styles, see the .NET Framework documentation for the System.Globalization.NumberStyles and DateTimeStyles enumerations.
MSDN on System.Globalization.NumberStyles
Note that the float type has Allow Thousands = 0.
In other words, that may be inconvenient, but it works just like they say it will work.
March 18, 2014 at 1:31 am
Thanks for clarifying this!
So, it's not a bug, but rather a poor design decision 🙂
Reading the page you posted, leads me to another question: the AllowThousands entry specifies a 0 (=NO) for FLOAT but also for INTEGER.
But apparently that INTEGER does not match the int-types from SQL
;
WITH ListValues AS ( SELECT '1,234' AS StringValue UNION ALL SELECT '1234' )
SELECTStringValue
,TRY_PARSE( StringValue AS smallint )SMALLINT
FROMListValues
StringValue SMALLINT
----------- --------
1,234.56 1234
1234.56 1234
"Walking on water and developing software from a specification are easy if both are frozen." -- Edward V. Berard, "Life-Cycle Approaches"
March 18, 2014 at 6:47 am
Check the first link again. Sql links the int types to NumberStyles.Number, not NumberStyles.Integer.
March 18, 2014 at 6:54 am
Nevyn (3/18/2014)
Check the first link again. Sql links the int types to NumberStyles.Number, not NumberStyles.Integer.
My mistake, didn't catch that first link.
Both links cover what I need to know to communicatie further on.
Thanks.
"Walking on water and developing software from a specification are easy if both are frozen." -- Edward V. Berard, "Life-Cycle Approaches"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply