Groups | Search | Server Info | Keyboard shortcuts | Login | Register

Groups > comp.databases.ms-sqlserver > #197

Re: Looking for faster integer validation

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Looking for faster integer validation "Fred." <ghrno-google@yahoo.com> - 2011-04-23 15:02 -0700
  Re: Looking for faster integer validation Erland Sommarskog <esquel@sommarskog.se> - 2011-04-24 11:11 +0200
    Re: Fred. (ghrno-google@yahoo.com) writes:isnumeric has always been useless. Pedro Eu <pedro.wtf@gmail.com> - 2011-05-10 18:19 +0000
      Re: Fred. (ghrno-google@yahoo.com) writes:isnumeric has always been useless. Gene Wirchenko <genew@ocis.net> - 2011-05-10 12:01 -0700
      Re: Fred. (ghrno-google@yahoo.com) writes:isnumeric has always been useless. "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2011-05-10 14:56 -0400
      Re: Fred. (ghrno-google@yahoo.com) writes:isnumeric has always been useless. "Fred." <ghrno-google@yahoo.com> - 2011-05-10 12:20 -0700
      Re: Fred. (ghrno-google@yahoo.com) writes:isnumeric has always been useless. "Fred." <ghrno-google@yahoo.com> - 2011-05-10 12:50 -0700

csiph-web