Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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

Re: Fred. (ghrno-google@yahoo.com) writes:isnumeric has always been useless.

Show all headers | View raw

Fred, I'm trying to use your solution but I think something is going wrong. Try this:

IF 'abd' LIKE '%b%'
BEGIN
	PRINT '1st case OK'
END

IF 'aaa' LIKE '%b%'
BEGIN
	PRINT '2nd case OK'
END

IF '123' LIKE '%[^0-9]%'
BEGIN
	PRINT '3rd case OK'
END

IF '123a' LIKE '%[^0-9]%'
BEGIN
	PRINT '4th case OK'
END

Sql Server result:
1st case OK
4th case OK

The 1st case is ok to me, but I do not understood why the 3rd case is not ok and the 4th is.

Any idea?


> On Saturday, April 23, 2011 6:02 PM Fred. wrote:

> 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 had not 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 have 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.


>> On Sunday, April 24, 2011 5:11 AM Erland Sommarskog wrote:

>> Fred. (ghrno-google@yahoo.com) writes:
>> 
>> 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