T-SQL & Development
Fifteen Things I Hate About ISNUMERIC
Yello!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
SELECT ISNUMERIC('$') AS [What] UNION ALL SELECT ISNUMERIC('£') AS [What] UNION ALL SELECT ISNUMERIC(',') AS [What] UNION ALL SELECT ISNUMERIC('.') AS [What] UNION ALL SELECT ISNUMERIC('0e+99') AS [What] UNION ALL SELECT ISNUMERIC('2e2') AS [What] UNION ALL SELECT ISNUMERIC('12D4') AS [What] UNION ALL SELECT ISNUMERIC(',1,1,1,1,1,1,1') AS [What] UNION ALL SELECT ISNUMERIC('-') AS [What] UNION ALL SELECT ISNUMERIC('+') AS [What] UNION ALL SELECT ISNUMERIC(CHAR(9)) AS [What] UNION ALL SELECT ISNUMERIC(CHAR(10)) AS [What] UNION ALL SELECT ISNUMERIC(CHAR(11)) AS [What] UNION ALL SELECT ISNUMERIC(CHAR(12)) AS [What] UNION ALL SELECT ISNUMERIC(CHAR(13)) AS [What] |
Thanks for reading!
Brent says: the funny part to me is that if you just try to union all of them, SQL Server throws its hands up:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
SELECT '$' AS [What] UNION ALL SELECT '£' AS [What] UNION ALL SELECT ',' AS [What] UNION ALL SELECT '.' AS [What] UNION ALL SELECT '0e+99' AS [What] UNION ALL SELECT '2e2' AS [What] UNION ALL SELECT '12D4' AS [What] UNION ALL SELECT ',1,1,1,1,1,1,1' AS [What] UNION ALL SELECT '-' AS [What] UNION ALL SELECT '+' AS [What] UNION ALL SELECT ASCII(9) AS [What] UNION ALL SELECT ASCII(10) AS [What] UNION ALL SELECT ASCII(11) AS [What] UNION ALL SELECT ASCII(12) AS [What] UNION ALL SELECT ASCII(13) AS [What]; |
Result:
|
1 2 |
Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '$' to data type int. |
COME ON SQL SERVER YOU JUST TOLD ME THEY WERE ALL NUMERIC, dammit, convert to numeric for me if you’re so smart.
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
ISNUMERIC sucks, but TRY_CONVERT is awesome 🙂
If you have the version to use it of course!!
Hah, I have nearly the same demo that use to freak out the Devs when I spot them trying isnumeric!
I also ran Try_Convert with all the options to show them what a mess that is compared to ISNumeric. Enjoy guessing what works and why! Or sanitize yer darn data in .NET rather than asking SQL to do it!
DECLARE @Numbs TABLE([Test] VARCHAR(25), [Label] VARCHAR(25) NOT NULL, [Set] VARCHAR(25))
INSERT INTO @Numbs values (‘.’,’Period’,’Chars’),(‘,’,’Comma’,’Chars’),(‘-‘,’Minus’,’Chars’),(‘+’,’Plus’,’Chars’)
,(‘ ‘,’Tab’,’Chars’),(‘ ‘,’Space’,’Chars’),(NULL,’SQLNULL’,’Chars’)
,(”,’EmptyString’,’Chars’),(CHAR(0),’Char0′,’Chars’),(‘\’,’Backslash’,’Chars’)
,(CHAR(10),’Backspace’,’Chars’),(CHAR(11),’VerticalTab’,’Chars’)
,(CHAR(12),’FormFeed’,’Chars’),(CHAR(13),’Newline’,’Chars’)
,(‘$’,’Dollar’,’Currency’),(‘£’,’Pound’,’Currency’),(‘€’,’Euro’,’Currency’)
,(‘0E0′,’EmbeddedE’,’Notation’),(‘0D0′,’EmbeddedD’,’Notation’)
,(‘-0′,’NegZero’,’Mixed’),(‘- 0′,’NegSpaceZero’,’Mixed’),(‘-0e-0′,’NegENeg’,’Mixed’)
,(‘-.’,’NegPeriod’,’Mixed’),(‘-.,’,’NegPeriodComma’,’Mixed’),(‘-,.’,’NegCommaPeriod’,’Mixed’)
,(‘1111111111111111111111111′,’Long25′,’Long’),(‘11111111111111111111′,’Long20′,’Long’)
,(‘111111111111111′,’Long15′,’Long’),(‘1111111111′,’Long10′,’Long’),(‘11111′,’Long5′,’Long’)
,(‘333,444.1′,’EmbedComma’,’Separators’),(‘333.444,1′,’EmbedCommaTail’,’Separators’)
,(‘333.444.555′,’Embed2Period’,’Separators’),(‘333,444,555′,’Embed2Comma’,’Separators’)
,(‘1,234.567E8′,’EmbedCommaNotation’,’Separators’),(‘1,2,3,4,5,6′,’CommaList’,’Separators’)
,(‘,1,2,3,4,5,6′,’CommaListPrefix’,’Separators’)
select [Set], [Label], [Test], CAST([Test] AS VARBINARY(50)) AS [Hex], ISNUMERIC([Test]) AS [Is?]
, TRY_CONVERT(tinyint,[Test]) AS [TINYINT], TRY_CONVERT(smallint,[Test]) AS [SMALLINT], TRY_CONVERT(int,[Test]) AS [INT]
, TRY_CONVERT(bigint,[Test]) AS [BIGINT], TRY_CONVERT(decimal,[Test]) AS [DECIMAL], TRY_CONVERT(numeric,[Test]) AS [NUMERIC]
, TRY_CONVERT(float,[Test]) AS [FLOAT], TRY_CONVERT(real,[Test]) AS [REAL]
, TRY_CONVERT(smallmoney,[Test]) AS [SMALLMONEY], TRY_CONVERT(money,[Test]) AS [MONEY]
FROM @Numbs
ORDER BY [Set], [Label]
I was going to post just the int,decimal,float version of this and challenge people to guess which ones worked. But seeing your extended counter example list ; wtf on the leading comma vs non-leading comma on the comma separated ints….. anyone who scores more than about 5 points right has spent too much time doing hand coded sql ….
Heh heh heh…
hi Mark,
Thank you very much for a horrible example of isnumeric and TryConvert. I was not aware that try_convert was that bad.
That’s a great demo! What’s the bottom line that you give your devs?
P.S. CHAR(10) is line feed, not backspace
@Brent ascii() returns an int, although converting to numeric will still generate an error.
@Brent, I also beleive you were a bit fast on the trigger there, because the example with ISNUMERIC uses “CHAR(9)” where as the next example uses “ASCII(9)”, so they aren’t comparable 🙂
Anyway love your Posts, keep em comming 😉
Richard — that’s my fault, I updated the code after drafting the post.
Sorry about that!
I still have a few clients on 2008 so when testing for positive integers I use [SometimesAnInt] NOT LIKE ‘%[^0-9]%’
I stumbled upon this (https://stackoverflow.com/questions/4603292/check-if-a-varchar-is-a-number-tsql) a while back regarding issues with ISNUMERIC and a WIP solution…
Cheers
On that note, I still find it a critical oversight that one cannot reliably convert a string directly to a decimal when it’s in scientific notation. Forcing people to go through a float means the number can be corrupted. E.g. SELECT CONVERT(decimal(30, 15), CONVERT(float, ‘0.543e+2’)) —> 54.299999999999997
Granger — yep, I used to run into this problem a lot when importing Excel files. Never found a more reliable way.
[…] Out comes ISNUMERIC and all its failings. […]
[…] de SQL Server 2005 aquí y la búsqueda para «ISNUMERIC’ en la página). Ver comentarios aquí en el Brent Ozar del gran blog. No vuelvas a usarlo para cualquier propósito. […]
[…] Out comes ISNUMERIC and all its failings. […]
Always surprise how people, in this last century , have bigs facilities to say: “hey don’t do this, this really sucks !”
but can’t give a start of solution to do the job….
And this is not only apply in SQL….
Isnumeric Rocks !
This is very interesting and somewhat related to a question I am trying to find an answer to. We have a database for a financial application where all of the numeric data is defined with “approximate” numeric types such as float. The C# application uses the corresponding data types. We are refactoring portions of the application and developers want to convert the C# data types to “exact” numeric types such as Decimal. I seem to recall that converting from one to the other can really mess up the data, but I can’t seem to find any information about this. It might just be that I don’t know how to phrase my search. Do you have any suggestions on what to do in this situation? Or maybe there is a post on this blog you could refer me to?
For questions outside of the scope of the blog post, head to a Q&A site like https://dba.stackexchange.com or https://sqlservercentral.com.
[…] Continueremo questa parte introduttiva ricordando le funzioni T-SQL più utili per manipolare stringhe e date. Vedremo ad esempio come la funzione Try_convert può risolvere in molti casi gli annosi problemi di conversione e superare l’utilizzo non sempre chiarissimo di IsNumeric. Dai un’occhiata a questa foto o leggi l’articolo a riguardo di Brent Ozar https://www.brentozar.com/archive/2018/02/fifteen-things-hate-isnumeric/. […]