Find Rows in table with a value that cannot convert to int

Question

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

The system I'm working on uses an editable varchar field in one place, and joins that to an integer column in another place. If the varchar field gets an invalid number in it (something blank, with alpha or symbols, or would be a number over 2.1 billion (max int size)), the other query fails, saying the value xyz overflowed an int column, or could not be converted.

I came up with the following partial solution to find the offending 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, it could be defeated if your varchar value exceeds 'bigint'. Is there a more generic method to locate these type of records, instead of using cast/convert? It would be awesome if there was a built-in method for "IsInt()" but alas...

Answer 1

Starting with SQL Server 2012 you can use the TRY_CAST expression which returns NULL when cast cannot be performed:

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

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

Also there is similar TRY_CONVERT expression which has the same syntax as CONVERT, but requires to change DB compatibility level to 120 or greater, while TRY_CAST works with 100 as well.

Answer 2

If you are only interested in INTs then you can use PATINDEX('[^0-9]', g.membernumber) to test if the string contains any non-numeric characters. Then, you would use LEN to make sure the string is not greater than 10 characters, and then try to convert it to bigint. So, the WHERE condition 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 evaluation. That is why I used CASE in the where clause

SQLFiddle