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

Re: Looking for faster integer validation

Path	csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!aioe.org!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail
From	Erland Sommarskog <esquel@sommarskog.se>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: Looking for faster integer validation
Date	Sun, 24 Apr 2011 11:11:05 +0200
Organization	Erland Sommarskog
Lines	36
Message-ID	<Xns9ED171C6FB504Yazorman@127.0.0.1> (permalink)
References	<c237d892-745b-4235-8a2c-3c211c4d2f84@hg8g2000vbb.googlegroups.com>
Mime-Version	1.0
Content-Type	text/plain; charset=windows-1252
Content-Transfer-Encoding	8bit
Injection-Info	mx02.eternal-september.org; posting-host="DD6dU+BfJNjsjSP4/K/V7w"; logging-data="21703"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX18gk7VyrFZPBEfaD40Yi9Ut"
User-Agent	Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock	sha1:HAjKYFG7R7v9piDLNkBJ4ylbFaI=
Xref	x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:197

Show key headers only | 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