Convert varchar to float IF ISNUMERIC

Question

I have a column that contains characters and numbers  12 13 14 19K YR 22   So the column type is varchar  But then I m also doing some computations with this column  so I m trying to convert the data to float if it is numeric   This gives me an error though   SELECT CASE ISNUMERIC QTY            WHEN 1 THEN CAST QTY AS float            ELSE QTY         END AS test

User · Answer

I found this very annoying bug while converting EmployeeID values with ISNUMERIC:

SELECT DISTINCT [EmployeeID],
ISNUMERIC(ISNULL([EmployeeID], '')) AS [IsNumericResult],

CASE WHEN COALESCE(NULLIF(tmpImport.[EmployeeID], ''), 'Z')
    LIKE '%[^0-9]%' THEN 'NonNumeric' ELSE 'Numeric'
END AS [IsDigitsResult]
FROM [MyTable]

This returns:

EmployeeID IsNumericResult MyCustomResult
---------- --------------- --------------
           0               NonNumeric
00000000c  0               NonNumeric
00D026858  1               NonNumeric

(3 row(s) affected)

Hope this helps!

User · Answer

You can t cast to float and keep the string in the same column  You can do like this to get null when isnumeric returns 0   SELECT CASE ISNUMERIC QTY  WHEN 1 THEN CAST QTY AS float  ELSE null END

User · Answer

-- TRY THIS --   select name  case when isnumeric empname   1 then  numeric  else  notmumeric  end from  Employees    But conversion is quit impossible   select empname  case when isnumeric empname   1 then empname else  notmumeric  end from  Employees

User · Answer

extending Mikaels  answers  SELECT   CASE WHEN ISNUMERIC QTY    e0     1 THEN CAST QTY AS float  ELSE null END AS MyFloat   CASE WHEN ISNUMERIC QTY    e0     0 THEN QTY ELSE null END AS MyVarchar FROM          Two data types requires two columns Adding e0 fixes some ISNUMERIC issues  such as   -   and empty string being accepted

[sql] Convert varchar to float IF ISNUMERIC

Examples related to sql

Examples related to sql-server

Examples related to tsql