Find rows in a table with a value that cannot be converted to int

Question

I need a way to detect rows in a table in MSSQL using varchar entries that cannot be converted to int.

The system I'm working on uses the editable varchar field in one place and joins the integer column in another place. If the varchar field receives an invalid number in it (something is empty, with alpha or characters, or will be more than 2.1 billion (maximum int size)), another request fails, saying that the xyz value overflowed the int column or may not converted.

I came up with the following partial solution for finding offensive records:

select g.id, g.membernumber from groups g left join secondary_groups sg on sg.id=g.id where convert(bigint, g.membernumber) > 2147483647 or isnumeric(g.membernumber) = 0 

This works fine for most things, but then I realized that it could be defeated if the varchar value exceeds 'bigint'. Is there a more general method for finding these types of records instead of using cast / convert? It would be great if there was a built-in method for "IsInt ()", but alas ...

Answer 1

If you are only interested in INT, you can use PATINDEX ('[^ 0-9]', g.membernumber) to check if the string contains any non-numeric characters. Then you should use LEN to make sure the string does not exceed 10 characters, and then try to convert it to bigint. So, the WHERE clause will look like this:

 where 1 = CASE WHEN patindex('%[^0-9]%', g.membernumber) > 0 THEN 1 WHEN LEN(g.membernumber) > 10 THEN 1 WHEN convert(bigint, g.membernumber) > 2147483647 THEN 1 WHEN LEN(g.membernumber) = 0 THEN 1 --Empty string causes error on convert ELSE 0 END 

SQL Server CASE works as a short circuit assessment. This is why I used CASE in the where clause

SQLFiddle

Answer 2

Starting with SQL Server 2012, you can use the TRY_CAST expression, which returns NULL when execution cannot be executed:

 select * from table where TRY_CAST(Value as int) is null 

Note that TRY_CAST uses the same casting rules as CAST , for example. it converts white space to 0. This is not a problem in your case, but it should be considered when the result of the conversion is used outside of SQL.

There is also a similar expression TRY_CONVERT , which has the same syntax as CONVERT , but requires changing the database compatibility level to 120 or more, and TRY_CAST works with 100.