{"id":149,"date":"2009-07-01T11:56:43","date_gmt":"2009-07-01T09:56:43","guid":{"rendered":"http:\/\/www.calvert.ch\/maurice\/?p=149"},"modified":"2012-08-13T22:20:05","modified_gmt":"2012-08-13T20:20:05","slug":"ms-sql-server-isnumeric-bug-the-joys-of-separators","status":"publish","type":"post","link":"https:\/\/www.calvert.ch\/maurice\/2009\/07\/01\/ms-sql-server-isnumeric-bug-the-joys-of-separators\/","title":{"rendered":"MS SQL Server ISNUMERIC bug &#8211; the joys of separators"},"content":{"rendered":"<p>From an early age I have practised defensive programming. On a recent project, I was loading data entered on an intranet site to an SQL Server table. Wary, I used SQL&#8217;s ISNUMERIC function to validate the numbers users had entered. It worked fine for a few weeks, until somone entered a comma as a decimal separator. It turns out that ISNUMERIC accepts this, whereas casting doesn&#8217;t. Here&#8217;s the proof:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-150\" src=\"http:\/\/www.calvert.ch\/maurice\/files\/2009\/07\/isnumeric.jpg\" alt=\"isnumeric\" width=\"418\" height=\"441\" srcset=\"https:\/\/www.calvert.ch\/maurice\/files\/2009\/07\/isnumeric.jpg 418w, https:\/\/www.calvert.ch\/maurice\/files\/2009\/07\/isnumeric-284x300.jpg 284w\" sizes=\"auto, (max-width: 418px) 100vw, 418px\" \/><\/p>\n<p>Doubtless, MS will try and wriggle out by saying that it depends on how your international settings are made. My contention is that if ISNUMERIC says it is then you should be able to CAST it to a number.<\/p>\n<p>Period.\u00a0<\/p>\n<p>(sorry for the weak pun)<\/p>\n","protected":false},"excerpt":{"rendered":"<p>From an early age I have practised defensive programming. On a recent project, I was loading data entered on an intranet site to an SQL Server table. Wary, I used SQL&#8217;s ISNUMERIC function to validate the numbers users had entered. <a href='https:\/\/www.calvert.ch\/maurice\/2009\/07\/01\/ms-sql-server-isnumeric-bug-the-joys-of-separators\/' class='excerpt-more'>[&#8230;]<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[47,11],"tags":[35],"class_list":["post-149","post","type-post","status-publish","format-standard","hentry","category-programming","category-technology","tag-sql","category-47-id","category-11-id","post-seq-1","post-parity-odd","meta-position-corners","fix"],"_links":{"self":[{"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/posts\/149","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/comments?post=149"}],"version-history":[{"count":2,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/posts\/149\/revisions"}],"predecessor-version":[{"id":608,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/posts\/149\/revisions\/608"}],"wp:attachment":[{"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/media?parent=149"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/categories?post=149"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/tags?post=149"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}