Problem of "IsNumeric" Function

IsNumeric is wonderful function, it allows you easily separate numbers and text within a column

Look for the following example:

;WITH SampleData AS (SELECT '1' AS TestColumn UNION SELECT 'It is not a number') SELECT CASE ISNUMERIC(TestColumn) WHEN 1 THEN CAST(TestColumn AS INT) END NumericColumn     , CASE ISNUMERIC(TestColumn) WHEN 0 THEN TestColumn END NonNumericColumn     , ISNUMERIC(TestColumn) AS ISNUMERIC_FUNCTION FROM SampleData;

However, that not always works. The following script will produce an error.

;WITH SampleData AS (SELECT '1' AS TestColumn UNION SELECT '.' UNION SELECT '-' UNION SELECT '    ') SELECT CASE ISNUMERIC(TestColumn) WHEN 1 THEN CAST(TestColumn AS FLOAT) END NumericColumn     , CASE ISNUMERIC(TestColumn) WHEN 0 THEN TestColumn END NonNumericColumn     , ISNUMERIC(TestColumn) AS ISNUMERIC_FUNCTION FROM SampleData;

Why?
Just replace CASE Statements and see what happens

;WITH SampleData AS (SELECT '1' AS TestColumn UNION SELECT '.' UNION SELECT '-' UNION SELECT '    ') SELECT TestColumn, ISNUMERIC(TestColumn) AS ISNUMERIC_FUNCTION FROM SampleData;

What the hell? Why ISNUMERIC returns "1" for not really numeric strings?

Let's do a research on what SQL Server claims as "numbers":

DECLARE @i INT = 256 WHILE @i > 0 BEGIN   SET @i -= 1;     IF @i NOT BETWEEN 48 AND 57 AND    ISNUMERIC(CHAR(@i)) = 1         PRINT CAST(@i AS VARCHAR) + ' = "' + CHAR(@i) + '"'; END

Now that makes sense.
At first SQL considered as numbers all values, which might represent currency: "¥","¤","£","¢","$","€"

It also considered as numbers characters, which can be used in numbers: ",",".","-","+"

Still it is not very clear why "\", "TAB", "Carriage return", etc. counted as numbers.
These symbols do not impact numbers only if they are used as the very first symbol

The best way to overcome IsNumeric function problem is to use TRY_CONVERT function:

;WITH SampleData AS (SELECT '1' AS TestColumn UNION SELECT '.' UNION SELECT '2A' UNION SELECT '-5' UNION SELECT '0.3') SELECT TestColumn, ISNUMERIC(TestColumn) AS ISNUMERIC_FUNCTION     , CASE WHEN TRY_CONVERT(FLOAT, TestColumn) IS NULL THEN 'It Is Not a Number' ELSE 'It Is Number' END AS Is_It_Number     , TRY_CONVERT(FLOAT, TestColumn)     , ISNULL(TRY_CONVERT(FLOAT, TestColumn),0) FROM SampleData;

You can make determination if Cell value numeric or not,
You can immediately convert value to a number with having "Null" for not-numeric values
Or you can replace non-numeric values by "0" or any other number using "ISNULL" function.

Wonderful?
Sure!

But not perfect. TRY_CONVERT function is available only starting SQL Server version 2012.
For old SQL Servers developers still have to write special case statements

Also, do not forget, even if you are running latest SQL Server edition that functionality won't work if your current database is still in old compatibility level
Your compatibility level has to be bigger than 100:

SELECT compatibility_level FROM sys.databases WHERE DATABASE_id = DB_ID();