Geek Answers Handbook

Cannot distinguish value as float

We have SQL that performs the CAST function (for FLOAT) in ColumnA. SQL has a filter that, in the end, will indirectly filter out rows that have non-numeric values ​​in ColumnA. However, due to what, in my opinion, is related to running part of SQL in paralell, I believe that CAST applies even to filtered strings, and this leads to SQL crashing in that "it is impossible to distinguish the value as a float ... "

I know that if I run one proc, adding a query hint

OPTION (MAXDOP 1)

that SQL is working as expected. I suspect running on 1 proc causes the filter to be used to trim a row with non-numeric values ​​in column A, so that the CASTING result of its values ​​succeed. I also found that using the query hint

OPTION (FORCE ORDER)

fixes the problem, I suppose, because it also ensures that the filter is applied first, and I get much better query performance that works on one cylinder.

I cant fix the problem using the second option. If I have any misconceptions as to what is going on here, or if someone would like to state my general approach or make a recommendation, I would appreciate it.

I'm running on

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1720.0 (X64) June 12, 2010 01:34:59 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: package updates 2)

The following thought:

It seems to be nice that T-SQL has the following functions to check if a string can be converted to a specific data type.

IsFloat IsNumeric IsInteger ..

, , varchar (255). , " !"

+5
2

.

, , T-SQL : , .

SQL Server 2012 TRY_CONVERT. , NULL, .

SELECT TRY_CONVERT ( FLOAT, 'Fish')

, WHERE , SELECT. SQL Server 2005, , , , . " SQL Server 2005" .

SQL Server 2005 , SQL Server 2000. :

  • , .
  • .

.

2012 TRY_CONVERT CAST AS FLOAT CASE. .

  SELECT CASE WHEN ISNUMERIC(Col)=1 THEN CAST(Col AS FLOAT) END AS Col
  FROM Table
  WHERE ISNUMERIC(Col)=1

- , , ISNUMERIC , , float , , '.'

CASE ( )

/ SQL Server SQLKiwi

+4

, . CONVERT() , " " .

, , . , . (, - .)

, SQL Server IsNumeric.

IsNumeric , , , "" IsNumeric-, , .

:

select convert(float,case when isnumeric( t.foo )=1 then t.foo else null end)

.

, "" , .

select convert(float,case when t.fi in ('fo','fum') then t.foo else null end)
+1

More articles:


All Articles