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

Looking for faster integer validation

Path	csiph.com!x330-a1.tempe.blueboxinc.net!feeder1.hal-mli.net!nx02.iad01.newshosting.com!newshosting.com!69.16.185.16.MISMATCH!npeer02.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!postnews.google.com!hg8g2000vbb.googlegroups.com!not-for-mail
From	"Fred." <ghrno-google@yahoo.com>
Newsgroups	comp.databases.ms-sqlserver
Subject	Looking for faster integer validation
Date	Sat, 23 Apr 2011 15:02:06 -0700 (PDT)
Organization	http://groups.google.com
Lines	20
Message-ID	<c237d892-745b-4235-8a2c-3c211c4d2f84@hg8g2000vbb.googlegroups.com> (permalink)
NNTP-Posting-Host	76.235.48.99
Mime-Version	1.0
Content-Type	text/plain; charset=ISO-8859-1
X-Trace	posting.google.com 1303596126 4445 127.0.0.1 (23 Apr 2011 22:02:06 GMT)
X-Complaints-To	groups-abuse@google.com
NNTP-Posting-Date	Sat, 23 Apr 2011 22:02:06 +0000 (UTC)
Complaints-To	groups-abuse@google.com
Injection-Info	hg8g2000vbb.googlegroups.com; posting-host=76.235.48.99; posting-account=H0CbGQoAAACbl_tS1RfYwvMt_jmh18zO
User-Agent	G2/1.0
X-HTTP-UserAgent	Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Trident/4.0; .NET CLR 1.0.3705; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; Media Center PC 4.0),gzip(gfe)
Xref	x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:196

Show key headers only | View raw

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.

Fred.

Back to comp.databases.ms-sqlserver | Previous | Next — 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