Looping through a column to check if a cast succeeds in SQL Server-CodePudding

I am trying to check if an entire column which is varchar and make sure it can be cast to float. I have a cursor portion like so:

        DECLARE @CastFailed BIT

        SET @CastFailed = (SELECT SUM(CASE WHEN TRY_CAST(@ColumnName AS FLOAT) IS NULL THEN 1
            ELSE 0 END) AS CastResult)

        -- Look at this
        PRINT @CastFailed

        IF @CastFailed > 0
        BEGIN

            PRINT 'ERROR: '   @ColumnName   ' cannot be converted to FLOAT type'

            SET @HasErrors = 1
   
        END
        ELSE
        BEGIN

            PRINT 'The cast has passed.'

        END

For some reason, it is always returning 1. I already in a previous part of the cursor (not shown but above), verified that the column passed in (@ColumnName) is NOT NULL at any point.

I need to find out if all the CAST to FLOAT for @ColumnName are valid. The cursor loops through a table of columns bring in the FETCH @ColumnName one by one. What am I missing?

CodePudding user response：

Here is an option where you can avoid cursors and Dynamic SQL. It will dynamically UNPIVOT your data and return the columns which fail the conversion to float

(2008 & 2012 Compatible)

Example

Declare @YourTable Table (id int,[Col1] varchar(50),[Col2] varchar(50))  
Insert Into @YourTable Values 
 (1,'1e6','ABC')  -- This Col2 will fail Conversion
,(2,'5.5','25')
,(3,'50.25','0')

Select C.Col
      ,Failed = count(*)
 from  @YourTable A
 Cross Apply ( values ( convert(xml,(Select A.* for XML RAW)) ) )B(XMLData)
 Cross Apply ( 
                Select Col   = xAttr.value('local-name(.)', 'varchar(100)')
                      ,Value = xAttr.value('.','varchar(max)')
                 From  XMLData.nodes('//@*') xNode(xAttr)
             ) C
 Where Col in ('Col1','Col2')  -- Or you can Exclude Columns ...  Where Col NOT in ('id','OtherCols','ToExclude')
   and try_convert(float,value) is null
 Group BY C.Col

Results

Col     Failed
Col2    1

CodePudding user response：

Easy:

DECLARE @t TABLE (txt VARCHAR(100));
INSERT  @t VALUES ('ABC123'),('100.00'),('100'),('11.222.333'),('00');

DECLARE @CastFailed BIT = 
  (SELECT ISNULL(MAX(1),0) FROM @t AS t WHERE TRY_CAST(t.Txt AS FLOAT) IS NULL);

SELECT CastFailed = @CastFailed;

For even better performance ...

DECLARE @t TABLE (txt VARCHAR(100));
INSERT  @t VALUES ('ABC123'),('100.00'),('100'),('11.222.333'),('00');

DECLARE @CastFailed BIT = 
  (ISNULL((SELECT TOP(1) 1 FROM 
    (SELECT 1 FROM @t AS t WHERE TRY_CAST(t.Txt AS FLOAT) IS NULL) AS x(x)),0));

SELECT CastFailed = @CastFailed;

CodePudding user response：

If you are trying to supply the column name dynamically, you'll need dynamic SQL for this. Here's one way:

DECLARE @sql nvarchar(max), 
        @CastFailed bit,
        @ColumnName sysname = N'SomeColumnName';

SET @sql = N'SELECT @CastFailed = MAX(CASE 
   WHEN TRY_CONVERT(float, '   QUOTENAME(@ColumnName)   N') IS NULL
   THEN 1 ELSE 0 END) 
   FROM dbo.SomeTable;';

EXEC sys.sp_executesql @sql, N'@CastFailed BIT OUTPUT', @CastFailed OUTPUT;
SELECT @ColumnName, @CastFailed;

Another way to do this without ooping is to UNPIVOT (though you'll still need dynamic SQL). Basically the query you want is:

;;/* semi-colons for safety! */;;WITH x AS 
(
  SELECT 
    col1 = MAX(CASE WHEN TRY_CONVERT(float, col1) IS NULL THEN 1 ELSE 0 END),
    col2 = MAX(CASE WHEN TRY_CONVERT(float, col2) IS NULL THEN 1 ELSE 0 END)
  FROM dbo.SomeTable
)
SELECT col, CastFailed FROM x
UNPIVOT (CastFailed FOR col IN ([col1], [col2])) AS u;

And to do that dynamically:

DECLARE @table nvarchar(600) = N'dbo.SomeTable';

DECLARE @cols nvarchar(max), 
        @expr nvarchar(max),
        @sql  nvarchar(max);
        
SELECT @cols = STRING_AGG(QUOTENAME(name), N','),
       @expr = STRING_AGG(CONCAT(QUOTENAME(name), ' = MAX('
               N'CASE WHEN TRY_CONVERT(float,', QUOTENAME(name),
               N') IS NULL THEN 1 ELSE 0 END)') , N',')
  FROM sys.columns
  WHERE [object_id] = OBJECT_ID(@table);
  
SET @sql = N';;WITH x AS (SELECT '   @expr   N' FROM '   @table 
    N') SELECT col, CastFailed FROM x
    UNPIVOT (CastFailed FOR col IN ('   @cols   N')) AS u;';
  
EXEC sys.sp_executesql @sql;

All examples shown in this db<>fiddle.

(If you are really are using 2008 and/or 2012 you'll have to change STRING_AGG() to FOR XML PATH but the concept is the same.)