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

Can you tell us what the error is? – Jim May 23 '11 at 03:44 — Jim, May 23 '11 at 03:44

score 33 · Accepted Answer · answered May 23 '11 at 03:46

33

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

answered May 23 '11 at 03:46

Mikael Eriksson

136,425
22
210
281

1

+1: Beat me to it - a column can not be more than one data type – OMG Ponies May 23 '11 at 03:48
6

Except, of course, for the fact that yet again ISNUMERIC falls down: `SELECT CASE ISNUMERIC('£2') WHEN 1 THEN CAST('£2' AS float) ELSE null END`. Has anyone ever discovered a circumstance where ISNUMERIC is usable without some fudging? – Damien_The_Unbeliever May 23 '11 at 04:45
In SQL Server 2012+, you can use TRY_CONVERT(): – mugume david Feb 08 '20 at 11:33

score 13 · Answer 2 · edited May 23 '17 at 12:00

13

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

edited May 23 '17 at 12:00

Community

1
1

answered May 23 '11 at 04:44

gbn

422,506
82
585
676

Unlike the usual questions involving `int`s, this one involves conversion to `float` - so what if the QTY value is already `'1.5e10'` (which can be converted to float)? – Damien_The_Unbeliever May 23 '11 at 04:48
@Damien_The_Unbeliever: it fails in this case. Personally I use decimals mostly so never had the need to work it out fully – gbn May 23 '11 at 04:55

score 2 · Answer 3 · answered Oct 23 '13 at 23:58

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!

jack.mike.info · Answer 4 · 2011-05-23T10:46:12.603

-1

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

edited May 23 '11 at 10:46

answered May 23 '11 at 10:36

jack.mike.info

128
3

Convert varchar to float IF ISNUMERIC

4 Answers4

Linked