{"id":71444,"date":"2017-06-23T16:43:44","date_gmt":"2017-06-23T16:43:44","guid":{"rendered":"https:\/\/www.simple-talk.com\/?p=71444"},"modified":"2021-09-29T16:21:12","modified_gmt":"2021-09-29T16:21:12","slug":"time-stop-using-isnumeric","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/time-stop-using-isnumeric\/","title":{"rendered":"SQL Server IsNumeric vs TRY_CAST and TRY_CONVERT: Why IsNumeric Gives Misleading Results"},"content":{"rendered":"

IsNumeric(), the unloved system function.<\/h2>\n

The system function IsNumeric() is so unloved that I feel compelled to say something in its\u2019 defence. It is more or less giving you the correct answer, but to a different question to the one that you\u2019re asking, and what it tells you isn\u2019t very helpful.<\/p>\n

IsNumeric() is actually answering the question \u2018Can this string be converted or \u2018cast\u2019 to a numeric datatype by SQL Server?\u2019 It is pretty good at getting this answer right but it doesn\u2019t tell you which datatype. It also isn\u2019t interested in such things as overflow.<\/p>\n

This problem of IsNumeric() was solved in SQL Server 2012. There was no way of morphing IsNumeric() intro something more valuable, so Try_Cast(), Try_Parse(), and Try_Convert() were introduced. The introduction of these system functions really solve the problem, unless you are still on earlier versions than SQL Server 2012. I\u2019ll show how to do the same thing in previous versions of SQL Server, and demonstrates one or two tricks you can do with these functions.<\/p>\n

So how bad is IsNumeric()?<\/h2>\n

To answer this question we first need some test data, a table full of strings that might, or might not, be numbers<\/p>\n

CREATE TABLE #testData(Number nVARCHAR(30))\r\n  INSERT INTO #testData(number) values\r\n  (N' '),(N''),('2^2'),(N'  5105'),(N'$'),(N'$$'),(N'$-.'),(N'$50,003.25'),(N'+'),(N'+-'),(N'+-2'),\r\n  (N'+1'),(N'-'),(N'-0'),(N'-0D0'),(N'-0D0-'),(N'-1'),(N'-1.18D38'),(N'-1.18E38'),(N'-1.18E-38'),\r\n  (N'-19.4.9'),(N'-1d-1'),(N'-2.23E-308'),(N'-3.40E+38'),(N'-34.56'),(N'.'),(N'.-99'),(N'.56'),\r\n  (N'3,7,9'),(N'3,2'),(N'34,233'),(N'2,147,483,646'),(N'376,765'),(N'2,000,000,000'),(N'49,586'),\r\n  (N'\/'),(N'09876543'),(N'1'),(N'1+1'),(N'1.18E-38'),(N'1.79D+308'),(N'1.79E+308'),(N'13245'),\r\n  (N'1d1'),(N'1e'),(N'1e1'),(N'2.23E-308'),(N'234.56'),(N'254.45678'),(N'3 4'),(N'3.40E+38'),\r\n  (N'4.56.5'),(N'52,352.25'),(N'56--4'),(N'56.'),(N'56..'),(N'5D105'),(N'9-8'),(N'NULL'),(N'\\'),\r\n  (N'_'),(N'd'),(N'e'),(N'e1'),(N'\u00b9'),(N'\u00bd'),(N'\u20a0'),(N'\u20a4'),(N'\u20a6'),(N'\u20ac'),(N'\u2460'),(N'\u2488'),(N'?'),\r\n  (N'?'),(N'\ufdfc'),(N'\ufe69'),(N'\uff04'),(N'\uffe0'),(N'\uffe1'),(N'\uffe5')<\/pre>\n
Now, we can check to see if they really are numbers, or just strings by seeing whether the Try_Convert() function was successful or not.<\/p>\n
SELECT ''''+Number+'''' AS TheString, \r\n    COALESCE(\r\n  \tCASE WHEN TRY_CONVERT(INT,Number) IS NOT NULL THEN 'integer' END,\r\n  \t  CASE WHEN TRY_CONVERT(NUMERIC(14,4),Number) IS NOT NULL THEN 'numeric' END,\r\n  \t    CASE WHEN TRY_CONVERT(FLOAT,Number) IS NOT NULL THEN 'float' END,\r\n  \t      CASE WHEN TRY_CONVERT(MONEY,Number) IS NOT NULL THEN 'money' END,'string') AS [CanConvertTo]\r\n  FROM #testData AS TD<\/pre>\n
You\u2019ll see how a surprising number of these can be converted into numbers.<\/p>\n
<\/p>\n
Now we can see how well IsNumeric() gets it right.<\/p>\n
SELECT ''''+number+'''' AS TheString, datatype, IsItNumeric, \r\n    CASE WHEN IsItNumeric=0 AND datatype<>'string' THEN 'wrong' ELSE 'correct' END AS agreement, \r\n    Try_Convert(INT,number) AS AsInteger,\r\n    Try_Convert(NUMERIC(14,4),number) AS AsNumber,\r\n    Try_Convert(FLOAT,number) AS AsFloat,\r\n    Try_Convert(MONEY,number) AS AsMoney,\r\n    CASE WHEN datatype='string' THEN number ELSE NULL END AS AsString\r\n  FROM \r\n    (SELECT Number, IsNumeric(Number), \r\n     Coalesce(\r\n  \tCASE WHEN Try_Convert(INT,Number) IS NOT NULL THEN 'integer' END,\r\n  \t  CASE WHEN Try_Convert(NUMERIC(14,4),Number) IS NOT NULL THEN 'numeric' END,\r\n  \t    CASE WHEN Try_Convert(FLOAT,Number) IS NOT NULL THEN 'float' END,\r\n  \t      CASE WHEN Try_Convert(MONEY,Number) IS NOT NULL THEN 'money' END,'string') AS [datatype]\r\n  \tFROM #testData AS TD\r\n     )f(number,IsItNumeric,datatype)\r\n  WHERE IsItNumeric=0 AND datatype<>'string'<\/pre>\n
So the developers who complained have some justice, but in fact the errors are pretty minor, judging from my test data. IsNumeric() isn\u2019t aware that empty strings are counted as (integer)zero, and that some perfectly valid money symbols, by themselves, are not converted to (money)zero. Plenty of others are, as you\u2019ll see from the test data. This looks to me to be a sign that they simply haven\u2019t updated the function to accommodate the full Unicode money types.<\/p>\n
<\/p>\n
Even if you just wanted a simple yes\/no answer about \u2018whether a string can be converted to a number, but which datatype we\u2019re not telling you\u2019, then it is more reliable to use \u2026<\/p>\n
SELECT \r\n    CASE WHEN TRY_CONVERT(INT, Number) IS NOT NULL\r\n           OR TRY_CONVERT(NUMERIC(18, 6), Number) IS NOT NULL\r\n           OR TRY_CONVERT(FLOAT, Number) IS NOT NULL\r\n           OR TRY_CONVERT(MONEY, Number) IS NOT NULL THEN 1\r\n      ELSE 0\r\n    END AS PseudoIsInteger\r\n  FROM #testData AS TD<\/pre>\n
\u2026and you can hone this expression to your exact requirements. This will, for example, not tell you if it is a BIGINT or a NUMERIC(38,24) though, because it tests for overflow.<\/p>\n
But I\u2019m on an old version of SQL Server!!<\/h2>\n
No problem at all. We with grizzled muzzles have been avoiding IsNumeric() for many years and you can use the following code with any old version of SQL Server.<\/p>\n
 SELECT ''''+Number+'''' AS TheString, \r\n  Coalesce(\r\n    CASE \r\n      WHEN (Stuff(LTrim(Number),1,1,'') NOT LIKE N'%[^0123456789]%'--is it an integer\r\n        AND Left(LTrim(Number),1) LIKE N'[-+0123456789]' COLLATE Latin1_General_BIN)\r\n        OR Number=N'' COLLATE Latin1_General_BIN\r\n      THEN 'integer'  ELSE NULL END,\r\n    CASE \r\n      WHEN Stuff(LTrim(Number),1,1,'') NOT LIKE '%[^.0123456789]%' --is it numeric\r\n  \tAND Left(LTrim(Number),1) LIKE '[-.+0123456789]' \r\n  \tAND Number LIKE '%.%' AND Number NOT LIKE '%.%.%' \r\n  \tAND Number LIKE '%[0123456789]%'\r\n      THEN 'numeric' ELSE NULL END,\r\n    CASE  \r\n      WHEN Stuff(LTrim(Number),1,1,'') NOT LIKE '%[^-+.ED0123456789]%' --is it a float?\r\n        AND Left(LTrim(Number),1) LIKE '[-.+0123456789]' \r\n  \tAND Number LIKE '%[0123456789][ED][-+0123456789]%' \r\n  \tAND Right(Number ,1) LIKE N'[0123456789]'\r\n      THEN 'float' ELSE NULL  END,\r\n    CASE  \r\n      WHEN (Stuff(LTrim(Number),1,1,'') NOT LIKE '%[^-+.,0123456789]%'--is it money?\r\n  \tAND Number NOT LIKE '%.%.%' AND Number NOT LIKE '%[-+.]%[-+]%')\r\n        AND Number NOT LIKE '%[0123456789][-+][0123456789]%'\r\n        AND (Left(LTrim(Number),1) LIKE N'[$\u00a2\u00a3\u00a4\u00a5\u09f2\u09f3\u0e3f\u17db\u20a0\u20a1\u20a2\u20a3\u20a4\u20a5\u20a6\u20a7\u20a8\u20a9\u20aa\u20ab\u20ac\u20ad\u20ae\u20af\u20b0\u20b1\ufdfc\ufe69\uff04\uffe0\uffe1\uffe5\uffe6]' COLLATE Latin1_General_BIN\r\n\t  OR (Left(LTrim(Number),1) LIKE N'[-.\\+0123456789]' COLLATE Latin1_General_BIN))\r\n      THEN 'money' ELSE NULL END,\r\n      'string') AS Number_type\r\n  FROM #testData AS TD<\/pre>\n
This produces the same result with our test data as the Try_Convert() equivalent. You\u2019ll notice that there are four different tests which you can use, for an integer, numeric, float and money. You can now play along even on an old version of SQL Server but beware! As we soon show, the TRY_ family of functions also check for overflow.<\/p>\n
We can now provide a way of making sense of a very dodgy data import without risk of errors and can even filter out the data entries that need to be corrected.<\/p>\n
SELECT '''' + Number + '''' AS TheString, \r\n    Try_Convert(INT, Number) AS AsInteger,\r\n    Try_Convert(NUMERIC(18, 6), Number) AS AsNumber, \r\n    Try_Convert(FLOAT, Number) AS AsFloat, \r\n    Try_Convert(MONEY, Number) AS AsMoney\r\n  FROM #testData AS TD;<\/pre>\n
<\/p>\n
When you execute this, you\u2019ll see that some strings will successfully convert to a number type other than integer, which can then be converted to an integer. The same is true in other directions. If, therefore, you want to get the maximum of success in your data you will need to put in some logic to convert first to the most accommodating format and thence to your target format. You could lose precision or get an overflow error! You\u2019ll also want to define the type of integer and numeric to correspond with your requirements. Things can get more complex when you use Try_Parse() to import dates.<\/p>\n
Here is an obvious example where such a tactic is excellent and appropriate. A lot of integer data comes from the real world with commas. Rather than strip them out, you can parse them.<\/p>\n
SELECT --demonstrate a two-stage conversion\r\n     IsNumeric(number) AS IsItNumeric,\r\n     Coalesce(\r\n       Try_Convert(INT, Number),\r\n       Try_Convert(INT,Try_Convert(money, Number)),\r\n       NULL)  AS TheInteger\r\n FROM (VALUES(N'2,147,483,646'),(N'376,765'),(N'2,000,000,000'),(N'49,586'),\r\n      (N'0'),(N','),(N'2147483646'),(N'475'))test(number)<\/pre>\n
<\/p>\n
Another great advantage of the Try_Convert(), Try_Cast() and Try_Parse() datatypes over IsNumeric() is that they check for overflow as well as well as whether it represents a number of that datatype. This test confirms it<\/p>\n
SELECT --demonstrate that Try_Convert() checks for overflow\r\n  IsNumeric(number),\r\n  CASE WHEN Try_Convert(INT, Number) IS NOT NULL --is it an int?\r\n    THEN Convert(INT, Number) ELSE NULL END AS AsInteger\r\n FROM (VALUES(N'5555'),(N'55555'),(N'555555'),(N'5555555'),\r\n       (N'55555555'),(N'555555555'),(N'55555555555'),(N'555555555555'))test(number)<\/pre>\n
<\/p>\n
To use the wildcard matches for this purpose, as in our previous example, isn\u2019t possible. We can get a rough idea by counting the digits and then matching the most significant digit, but it isn\u2019t that accurate!<\/p>\n
BIGINTs range from -9,223,372,036,854,775,808) to 9,223,372,036,854,775,807, INTEGERs go from -2,147,483,648 to 2,147,483,647, whereas SMALLINTs range from -32,768 to 32,767 Bytes and lastly TINYINTs just go from 0 to 255. This means that you\u2019d have to construct a string check accordingly, remembering the complications of sign. It would usually be simpler to upgrade to SQL Server 2012!<\/p>\n
But I want to use my own rules!<\/h2>\n
You’ll agree that IsNumeric()<\/strong> is liberal with what it considers to be an Integer, Numeric, Float or Money datatype. This is because TRY_Convert<\/strong>(), Try_Parse<\/strong>() and Try_Cast() <\/strong>all obey more or less the same liberal rules. If you want something stricter, then you’ll need to combine the LIKE wildcard techniques with TRY_Convert(). As an example, this query\u00a0 disallows blank strings or + signs as meaning zero as well as anything else that cannot be converted into an integer.<\/p>\n
SELECT ''''+number+'''', TheInteger\r\nFROM (SELECT number, CASE\r\n      WHEN Stuff(LTrim(Number),1,1,'') NOT LIKE N'%[^0123456789]%'--is it an integer\r\n\t\tAND (LTrim(number) LIKE N'[-+][0123456789]%' COLLATE Latin1_General_BIN\r\n\t\t\tOR LTrim(number) LIKE N'[0123456789]%' COLLATE Latin1_General_BIN)\r\n      THEN Try_Convert(INTEGER,number)  ELSE NULL END AS TheInteger\r\n  FROM #testData)TD\r\nWHERE TheInteger IS NOT null<\/pre>\n
\"\"<\/p>\n
\u00a0Likewise, you might feel it unreasonable that an amount should be specified entirely without any number, just a currency sign. That is cool. \u00a0This would do the trick<\/p>\n
SELECT ''''+number+'''' AS TheString, TheMoneyAmount\r\nFROM (SELECT number, CASE \r\n       WHEN (Stuff(LTrim(Number),1,1,'') NOT LIKE '%[^-+.,0123456789]%'--is it money?\r\n\t    AND Number NOT LIKE '%.%.%' AND Number NOT LIKE '%[-+.]%[-+]%')--just one decimal point\r\n\t\tAND Number NOT LIKE '%[0123456789][-+][0123456789]%'-- sign in the middle\r\n        AND\t(Left(LTrim(Number),1) LIKE N'[$\u00a2\u00a3\u00a4\u00a5\u09f2\u09f3\u0e3f\u17db\u20a0\u20a1\u20a2\u20a3\u20a4\u20a5\u20a6\u20a7\u20a8\u20a9\u20aa\u20ab\u20ac\u20ad\u20ae\u20af\u20b0\u20b1\ufdfc\ufe69\uff04\uffe0\uffe1\uffe5\uffe6]'  COLLATE Latin1_General_BIN\r\n\t\t  OR (Left(LTrim(Number),1) LIKE N'[-.\\+0123456789]' COLLATE Latin1_General_BIN))\r\n        AND Number LIKE N'%[0123456789]%'--at least one number\r\n\tTHEN Try_Convert(Money,number)  ELSE NULL END AS TheMoneyAmount\r\n  FROM #testData)TD\r\nWHERE TheMoneyAmount IS NOT NULL\r\n<\/pre>\n
\"\"<\/p>\n
I hope that this makes the point that when you are filtering your input to ensure that correctly formed numbers are imported and anything that isn’t right is saved for amendment, you get a large measure of control by combining the two techniques<\/p>\n
Dates<\/h2>\n
I haven\u2019t mentioned dates, because this article is on the theme of IsNumeric() but the Try_Convert() function is a wonderful way of reading dates that are in a number of different formats. The SQL is scary, but so is the task of inputting dates successfully into SQL Server. This shows how it is done, but you may wish to vary the order in which you do the attempts according to your culture. What it shows is how you can do quite well with Try_Parse, even better with Try_Convert. But you can ace it with a coalesce function with a series of attempts. You would order those Try_Convert attempts in decreasing order of likelihood and with the correct orientation of the year for your culture and data before you try the reverse!<\/p>\n
SELECT DateString, \r\n   Try_Parse(DateString AS datetime2 USING 'en-Uk') AS tryParse,\r\n   Try_Convert(DATETIME2,DateString) AS TryConvert,\r\n   Coalesce(\r\n   Try_Convert(DATETIME2,DateString,0),  Try_Convert(DATETIME2,DateString,1), Try_Convert(DATETIME2,DateString,2), \r\n   Try_Convert(DATETIME2,DateString,3), Try_Convert(DATETIME2,DateString,4), Try_Convert(DATETIME2,DateString,5),\r\n   Try_Convert(DATETIME2,DateString,6), Try_Convert(DATETIME2,DateString,7), Try_Convert(DATETIME2,DateString,8), \r\n   Try_Convert(DATETIME2,DateString,9), Try_Convert(DATETIME2,DateString,10),Try_Convert(DATETIME2,DateString,11),\r\n   Try_Convert(DATETIME2,DateString,12), Try_Convert(DATETIME2,DateString,101), Try_Convert(DATETIME2,DateString,102),\r\n   Try_Convert(DATETIME2,DateString,103),Try_Convert(DATETIME2,DateString,104), Try_Convert(DATETIME2,DateString,105),\r\n   Try_Convert(DATETIME2,DateString,106),Try_Convert(DATETIME2,DateString,107), Try_Convert(DATETIME2,DateString,108),\r\n   Try_Convert(DATETIME2,DateString,109), Try_Convert(DATETIME2,DateString,110),Try_Convert(DATETIME2,DateString,111),\r\n   Try_Convert(DATETIME2,DateString,112), Try_Convert(DATETIME2,DateString,113),Try_Convert(DATETIME2,DateString,114),\r\n   Try_Convert(DATETIME2,DateString,120), Try_Convert(DATETIME2,DateString,121),Try_Convert(DATETIME2,DateString,126),\r\n   Try_Convert(DATETIME2,DateString,127), Try_Convert(DATETIME2,DateString,130))\r\nFrom\r\n (values( N'Jun 23 2017  3:57PM' ),(N'06\/23\/17' ),(N'17.06.23' ),( '23\/06\/17' ),(N'23.06.17' ),\r\n  (N'23-06-17' ),(N'23 Jun 17' ),(N'Jun 23, 17' ),(N'15:57:54' ),(N'Jun 23 2017  3:57:54:810PM' ),\r\n  (N'06-23-17' ),(N'17\/06\/23' ),(N'170623' ),(N'Jun 23 2017  3:57PM' ),(N'06\/23\/2017' ),\r\n  (N'2017.06.23' ),(N'23\/06\/2017' ),(N'23.06.2017' ),(N'23-06-2017' ),(N'23 Jun 2017' ),\r\n  (N'Jun 23, 2017' ),(N'15:57:54' ),(N'Jun 23 2017  3:57:54:810PM' ),(N'06-23-2017' ),\r\n  (N'2017\/06\/23' ),(N'20170623' ),(N'23 Jun 2017 15:57:54:810' ),(N'15:57:54:810' ),\r\n  (N'2017-06-23 15:57:54' ),(N'2017-06-23 15:57:54.810' ),(N'2017-06-23T15:57:54.810' ),\r\n  (N'2017-06-23T15:57:54.810' ),(N'29 \u0631\u0645\u0636\u0627\u0646 1438  3:57:54:810PM' )\r\n )sample(dateString)<\/pre>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
Why IsNumeric() returns true for values that can’t actually be cast to numeric types – currency symbols, commas, scientific notation, and unusual sign positions. Use TRY_CAST or TRY_CONVERT instead for reliable numeric validation. Includes test cases showing the difference and workarounds for SQL Server versions pre-2012.…<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,143531],"tags":[5134],"coauthors":[6813],"class_list":["post-71444","post","type-post","status-publish","format-standard","hentry","category-blogs","category-t-sql-programming-sql-server","tag-sql-prompt"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71444","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=71444"}],"version-history":[{"count":11,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71444\/revisions"}],"predecessor-version":[{"id":72241,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71444\/revisions\/72241"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=71444"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=71444"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=71444"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=71444"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}