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

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

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

Show all headers | View raw

On May 10, 2:19 pm, Pedro Eu <pedro....@gmail.com> wrote:
> 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-goo...@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, esq...@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- Hide quoted text -
>
> - Show quoted text -
PS, the query

SELECT	''''+S+'''' AS [STR],
		CASE
			WHEN S NOT LIKE '%[^0-9]%' THEN 'ALL DIGITS'
			ELSE 'NOT ALL DIGITS'
		END AS [TYPE]
FROM (	SELECT '0123456789' AS S UNION ALL
		SELECT '123X6' UNION ALL
		SELECT 'A' UNION ALL
		SELECT ''  UNION ALL
		SELECT '0' UNION ALL
		SELECT NULL
		) AS t

has the result set:

STR	TYPE
'0123456789'	ALL DIGITS
'123X6'	NOT ALL DIGITS
'A'	NOT ALL DIGITS
''	ALL DIGITS
'0'	ALL DIGITS
NULL	NOT ALL DIGITS

Note that if you want to eliminate the null string, you need an added
condition.

Fred.

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