CODEWITHSUNDEEP
sqlsql-servert-sql
Pod Mays
Pod Mays

Reputation: 2593

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

Upvotes: 15

Views: 128015

Answers (4)

LDawggie
LDawggie

Reputation: 441

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!

Upvotes: 2

jack.mike.info
jack.mike.info

Reputation: 128

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

Upvotes: -1

gbn
gbn

Reputation: 432311

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

Upvotes: 13

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Upvotes: 33

Related Questions