SQL Server - Cast invalid value to int

Question

Is there any way to deal with SQL casts if the input data is corrupt?

Let's say I have a column of datatype NVarchar(10) and want to cast this column to int.

Let's also say that some of the nvarchar values are corrupt, so they can't be converted to int.

Is there any way to silently ignore these, default them to 0 or some such?

SQL Server **2012** now has `TRY_PARSE`, `TRY_CAST` and `TRY_CONVERT` functions that do exactly what you're looking for. But those are in **2012** (and newer) only. — marc_s, Dec 22 '13 at 11:36
Is there any fallback? e.g. a case that matches for numeric first somehow? — Roger Johansson, Dec 22 '13 at 11:41

score 6 · Accepted Answer · answered Dec 22 '13 at 11:46

6

DECLARE @t TABLE (Numbers VARCHAR(20))
INSERT INTO @t
VALUES
('30a'),('30'),('100'),
('100a'),('200'),('200a')

SELECT CASE 
         WHEN ISNUMERIC(Numbers) = 1 
          THEN CAST(Numbers AS INT) ELSE NULL END AS Number
FROM @t

ISNUMERIC Function returns 1 when it is an integer value you can use this function.

Result

Number
NULL
30
100
NULL
200
NULL

it will cast the integer values to INT and ignore the values that cannot be cast to Int

answered Dec 22 '13 at 11:46

M.Ali

67,945
13
101
127

`Isnumeric()` function is not going to work because it will return true for some other characters that are not numbers. Here is a [demo](http://sqlfiddle.com/#!6/d41d8/13171) and [MSDN](http://technet.microsoft.com/en-us/library/ms186272.aspx) link for more details – Kaf Dec 22 '13 at 11:51
`ISNUMERIC` for the most part is useful, but remember that certain values won't get caught by the function. Sometimes you'll get values with an `e` in the middle, for instance, even though we don't want those values. – user123 Dec 22 '13 at 11:52
yep I was going to mention this as if you have `e` in your values it sees it as power of that integer left to it. `222e2` is seen as 222^2 – M.Ali Dec 22 '13 at 11:54
2

It is not just `e`, please [check this example](http://sqlfiddle.com/#!6/d41d8/13172) it has `$ 1` and still `ISNUMERIC()` returning `true`. – Kaf Dec 22 '13 at 11:57

Kaf · Answer 2 · 2013-12-22T11:47:32.177

4

Try this with PatIndex() function:

select id, val
from t
where  patindex('%[^0-9]%',val) = 0

Note: above query is filtering out corrupted values, if you need to bring them in with 0 values, please use a case expression as below.

select id, case when patindex('%[^0-9]%',val) = 0 
                then convert(int, val) 
           else 0 end val
from t

Fiddle demo for both queries

edited Dec 22 '13 at 11:47

answered Dec 22 '13 at 11:42

Kaf

33,101
7
58
78

score 2 · Answer 3 · edited May 23 '17 at 11:47

2

I'll be the unpopular one and advise REGEX because ISNUMERIC, while sometimes useful, doesn't catch everything. This answer on SO excellently covers some REGEX concepts, for instance:

One numeric digit

Probably the easiest one of the bunch:
WHERE Column LIKE '[0-9]'

For more details, here's a useful REGEX workbench by Phil Factor and Robyn Pae.

edited May 23 '17 at 11:47

Community

1
1

answered Dec 22 '13 at 11:55

user123

545
2
7
13

SQL Server - Cast invalid value to int

3 Answers3