2b831a25   
   From: esquel@sommarskog.se   
      
   Fred. (ghrno-google@yahoo.com) writes:   
   > I process a weekly extract which, unfortunately, comes from a table   
   > with a 20 unvalidated integer fields which I need in the form of   
   > numbers when they are valid.  Until the most recent update I'd been   
   > sliding by bulk importing the extract and then converting to a   
   > validated table using an append query cased on ISNUMERIC returning 1.,   
   > NULL otherwise for these fields.   
   >   
   > With the last update to SQL Server Standard, this finally caught up   
   > with me, when ISNUMERIC('\') started returning 1 (the value was in a   
   > record which hadn't been touched in some time).  I have a patch in   
   > which this particualr field in this record, and am working on a more   
   > robust solution.   
   >   
   > The best I've been able to come up with is a function which validates   
   > character by character and returns the converted value converts if   
   > valid.  This takes about 5 minutes per million records, more than   
   > original query (20 fields per record, 16 microseconds per field) which   
   > is sort of acceptable, but which I would like to improve.   
      
   isnumeric has always been useless.   
      
   If you need to validate for unsigned integers, you can use this expression:   
      
      col NOT LIKE '%[^0-9]%'   
      
   This expression returns false, as soon there is a value which contains any   
   non-digit character including space.   
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)