[sql] Convert varchar to float IF ISNUMERIC

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

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!

..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)

-- 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]