Path: csiph.com!x330-a1.tempe.blueboxinc.net!aioe.org!news.glorb.com!npeer03.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!post01.iad.highwinds-media.com!newsfe10.iad.POSTED!5788d997!not-for-mail
From: Pedro Eu <pedro.wtf@gmail.com>
Subject: Re: Fred. (ghrno-google@yahoo.com) writes:isnumeric has always been useless.
Newsgroups: comp.databases.ms-sqlserver
Reply-To: esquel@sommarskog.se
References: <Xns9ED171C6FB504Yazorman@127.0.0.1>
Message-ID: <2011510141922usenet@terrranews.com>
Lines: 77
X-Complaints-To: abuse@teranews.com
NNTP-Posting-Date: Tue, 10 May 2011 18:19:29 UTC
Organization: TeraNews.com
Date: Tue, 10 May 2011 18:19:29 GMT
Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:316

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