Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1314151617»»

Why doesn’t ISNUMERIC work correctly? (SQL Spackle) Expand / Collapse
Author
Message
Posted Tuesday, April 16, 2013 1:25 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:47 AM
Points: 65, Visits: 154
I love this article, but I'm bothered by the following scenario...

declare @var varchar(7)
set @var = ''

select case when @var NOT LIKE '%[^0-9]%'
then 'I am a number'
else 'I am not a number'
end as [result]

And the result is
result
-----------------
I am a number


Not what I was expecting, but by wrapping the var in a NULLIF function, it works out correctly.


declare @var varchar(7)
set @var = ''

select case when nullif(@var,'') NOT LIKE '%[^0-9]%'
then 'I am a number'
else 'I am not a number'
end as [result]

with the result
result
-----------------
I am not a number


Am I missing something, or is this the exception to the rule?
Post #1442972
Posted Tuesday, April 16, 2013 1:36 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:49 AM
Points: 22,511, Visits: 30,238
mark.gilbert (4/16/2013)
I love this article, but I'm bothered by the following scenario...

declare @var varchar(7)
set @var = ''

select case when @var NOT LIKE '%[^0-9]%'
then 'I am a number'
else 'I am not a number'
end as [result]

And the result is
result
-----------------
I am a number


Not what I was expecting, but by wrapping the var in a NULLIF function, it works out correctly.


declare @var varchar(7)
set @var = ''

select case when nullif(@var,'') NOT LIKE '%[^0-9]%'
then 'I am a number'
else 'I am not a number'
end as [result]

with the result
result
-----------------
I am not a number


Am I missing something, or is this the exception to the rule?


The empty string ('') is implicitly converted to a zero (0) which is why it returned 'I am a number'.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1442978
Posted Tuesday, April 16, 2013 1:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:57 AM
Points: 5,797, Visits: 8,013
mark.gilbert (4/16/2013)
Am I missing something, or is this the exception to the rule?

What Lynn said. :)
For ensuring that the empty string is not treated as numeric, I would personally not use NULLIF, but:
SELECT CASE WHEN @var <> '' AND @var NOT LIKE '%[^0-9]%' 
THEN 'I am a number'
ELSE 'I am not a number'
END AS Result;




Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1442981
Posted Tuesday, April 16, 2013 1:54 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:16 AM
Points: 8,289, Visits: 8,742
mark.gilbert (4/16/2013)
I love this article, but I'm bothered by the following scenario...

Do you think a zero length string contains a character which isn't in 0 to 9? Or that NULL can be like any string or pattern? It has to be one of the other for you to be unhappy, surely?


Tom
Post #1442991
Posted Tuesday, April 16, 2013 3:20 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:01 PM
Points: 342, Visits: 1,781
Hugo Kornelis (4/16/2013)
mark.gilbert (4/16/2013)
Am I missing something, or is this the exception to the rule?

What Lynn said. :)
For ensuring that the empty string is not treated as numeric, I would personally not use NULLIF, but:
SELECT CASE WHEN @var <> '' AND @var NOT LIKE '%[^0-9]%' 
THEN 'I am a number'
ELSE 'I am not a number'
END AS Result;



Hello Mark, et al. In as respectful of a tone as possible (cuz I know how easy it is to sound harsh / sarcastic in writing), both Lynn (who said that the behavior was due to the empty string being converted to a zero) and Hugo are incorrect with respect to the cause of the issue, though Hugo is correct in terms of how to best account for what is really happening.

The reason that conversion to a zero is not an issue here is that this is still a string comparison, even if using a numeric range in the form of [^0-9]. The real issue is the half-assed implementation of Regular Expressions that is provided by the LIKE operator; there is no way to specify how many of character range you are trying to match: it is always a single character position.

In true Regular Expressions, any particular "thing" to match can have a modifier to indicate how many occurrences of a "thing" should match. The modifiers are:
+ = 1 or more (but has to be at least one)
* = 0 or more
? = 0 or 1
{X,Y} = between X and Y
{X,} = at least X, but no maximum
{,Y} = 0 up to Y

In the case of the LIKE operator, the [] requires a single character. An empty string, by definition, does not have a single character, even one that would not match the "not 0 through 9". Hence, Hugo's suggestion is correct as it accounts for having 0 characters (which cannot be handled by the '%[]%' pattern that requires at least one character).

To see these points in action:

-- Yes, empty string does convert to zero when passed to a numeric datatype,
-- but not in a string comparison
SELECT CONVERT(INT, '') [ConvertedToZero],
CASE
WHEN '' LIKE '%[0-9]%' THEN 'Is a zero'
ELSE 'Not a zero'
END AS [NotConvertedToZero]

-- Returns: 0, Not a zero


-- Test empty string -> zero by changing pattern to match
declare @var2 varchar(7)
set @var2 = ''

-- shows "I am a letter" due to no characters;
-- implicit 0 would return "I am not a letter"
select case when @var2 NOT LIKE '%[^a-z]%'
then 'I am a letter'
else 'I am not a letter'
end as [result]

-- explicit 0 shows "I am not a letter" as expected
set @var2 = '0'
select case when @var2 NOT LIKE '%[^a-z]%'
then 'I am a letter'
else 'I am not a letter'
end as [result]

Of course, this is most likely all a moot point given that TRY_PARSE and TRY_CONVERT do a better job (in most cases) of determining Number vs Not a Number .


Take care,
Solomon...





SQL# - http://www.SQLsharp.com/
Post #1443027
Posted Tuesday, April 16, 2013 3:41 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:49 AM
Points: 22,511, Visits: 30,238
Solomon Rutzky (4/16/2013)
Hugo Kornelis (4/16/2013)
mark.gilbert (4/16/2013)
Am I missing something, or is this the exception to the rule?

What Lynn said. :)
For ensuring that the empty string is not treated as numeric, I would personally not use NULLIF, but:
SELECT CASE WHEN @var <> '' AND @var NOT LIKE '%[^0-9]%' 
THEN 'I am a number'
ELSE 'I am not a number'
END AS Result;



Hello Mark, et al. In as respectful of a tone as possible (cuz I know how easy it is to sound harsh / sarcastic in writing), both Lynn (who said that the behavior was due to the empty string being converted to a zero) and Hugo are incorrect with respect to the cause of the issue, though Hugo is correct in terms of how to best account for what is really happening.

The reason that conversion to a zero is not an issue here is that this is still a string comparison, even if using a numeric range in the form of [^0-9]. The real issue is the half-assed implementation of Regular Expressions that is provided by the LIKE operator; there is no way to specify how many of character range you are trying to match: it is always a single character position.

In true Regular Expressions, any particular "thing" to match can have a modifier to indicate how many occurrences of a "thing" should match. The modifiers are:
+ = 1 or more (but has to be at least one)
* = 0 or more
? = 0 or 1
{X,Y} = between X and Y
{X,} = at least X, but no maximum
{,Y} = 0 up to Y

In the case of the LIKE operator, the [] requires a single character. An empty string, by definition, does not have a single character, even one that would not match the "not 0 through 9". Hence, Hugo's suggestion is correct as it accounts for having 0 characters (which cannot be handled by the '%[]%' pattern that requires at least one character).

To see these points in action:

-- Yes, empty string does convert to zero when passed to a numeric datatype,
-- but not in a string comparison
SELECT CONVERT(INT, '') [ConvertedToZero],
CASE
WHEN '' LIKE '%[0-9]%' THEN 'Is a zero'
ELSE 'Not a zero'
END AS [NotConvertedToZero]

-- Returns: 0, Not a zero


-- Test empty string -> zero by changing pattern to match
declare @var2 varchar(7)
set @var2 = ''

-- shows "I am a letter" due to no characters;
-- implicit 0 would return "I am not a letter"
select case when @var2 NOT LIKE '%[^a-z]%'
then 'I am a letter'
else 'I am not a letter'
end as [result]

-- explicit 0 shows "I am not a letter" as expected
set @var2 = '0'
select case when @var2 NOT LIKE '%[^a-z]%'
then 'I am a letter'
else 'I am not a letter'
end as [result]

Of course, this is most likely all a moot point given that TRY_PARSE and TRY_CONVERT do a better job (in most cases) of determining Number vs Not a Number .


Take care,
Solomon...


Yes, you are correct. However, the empty string will convert to a 0 and a 0 is not like '%[^0-9]%' so it does pass the test where something is a number if does not contain values other than 0-9.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1443031
Posted Tuesday, April 16, 2013 4:15 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:01 PM
Points: 342, Visits: 1,781
Lynn Pettis (4/16/2013)
Solomon Rutzky (4/16/2013)
Hugo Kornelis (4/16/2013)
mark.gilbert (4/16/2013)
Am I missing something, or is this the exception to the rule?

What Lynn said. :)
For ensuring that the empty string is not treated as numeric, I would personally not use NULLIF, but:
SELECT CASE WHEN @var <> '' AND @var NOT LIKE '%[^0-9]%' 
THEN 'I am a number'
ELSE 'I am not a number'
END AS Result;



Hello Mark, et al. In as respectful of a tone as possible (cuz I know how easy it is to sound harsh / sarcastic in writing), both Lynn (who said that the behavior was due to the empty string being converted to a zero) and Hugo are incorrect with respect to the cause of the issue, though Hugo is correct in terms of how to best account for what is really happening.

The reason that conversion to a zero is not an issue here is that this is still a string comparison, even if using a numeric range in the form of [^0-9]. The real issue is the half-assed implementation of Regular Expressions that is provided by the LIKE operator; there is no way to specify how many of character range you are trying to match: it is always a single character position.

In true Regular Expressions, any particular "thing" to match can have a modifier to indicate how many occurrences of a "thing" should match. The modifiers are:
+ = 1 or more (but has to be at least one)
* = 0 or more
? = 0 or 1
{X,Y} = between X and Y
{X,} = at least X, but no maximum
{,Y} = 0 up to Y

In the case of the LIKE operator, the [] requires a single character. An empty string, by definition, does not have a single character, even one that would not match the "not 0 through 9". Hence, Hugo's suggestion is correct as it accounts for having 0 characters (which cannot be handled by the '%[]%' pattern that requires at least one character).

To see these points in action:

-- Yes, empty string does convert to zero when passed to a numeric datatype,
-- but not in a string comparison
SELECT CONVERT(INT, '') [ConvertedToZero],
CASE
WHEN '' LIKE '%[0-9]%' THEN 'Is a zero'
ELSE 'Not a zero'
END AS [NotConvertedToZero]

-- Returns: 0, Not a zero


-- Test empty string -> zero by changing pattern to match
declare @var2 varchar(7)
set @var2 = ''

-- shows "I am a letter" due to no characters;
-- implicit 0 would return "I am not a letter"
select case when @var2 NOT LIKE '%[^a-z]%'
then 'I am a letter'
else 'I am not a letter'
end as [result]

-- explicit 0 shows "I am not a letter" as expected
set @var2 = '0'
select case when @var2 NOT LIKE '%[^a-z]%'
then 'I am a letter'
else 'I am not a letter'
end as [result]

Of course, this is most likely all a moot point given that TRY_PARSE and TRY_CONVERT do a better job (in most cases) of determining Number vs Not a Number .


Take care,
Solomon...


Yes, you are correct. However, the empty string will convert to a 0 and a 0 is not like '%[^0-9]%' so it does pass the test where something is a number if does not contain values other than 0-9.


Hi Lynn. My point was that an empty string does not actually convert to a 0 (in this case) because there is no conversion: it is a string-to-string comparison. The original SQL as given by Mark is not a good test as it masks what is happening because there is no way to distinguish a supplied 0 from an implicit conversion to 0. Yes, it appears to do what you are saying but that particular pattern and usage does not allow for testing an opposite case. That is why I changed the pattern to be [^a-z] in the last two examples. If it were true that the empty string was being converted to a zero, then it would display "I am not a letter" (in the 2nd to last query) but instead it displays "I am a letter". And I included the very last example to show that setting the string to an explicit zero, which should behave the same as an implicit conversion of an empty string into 0, does not behave the same as the empty string.

Take care,
Solomon...





SQL# - http://www.SQLsharp.com/
Post #1443045
Posted Tuesday, April 16, 2013 8:37 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 35,978, Visits: 30,269
Solomon Rutzky (4/16/2013)
Of course, this is most likely all a moot point given that TRY_PARSE and TRY_CONVERT do a better job (in most cases) of determining Number vs Not a Number .


BWAAA-HAAAA!!!!! It's definitely NOT a moot point for those still using something less than 2012.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1443075
Posted Tuesday, April 16, 2013 9:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 35,978, Visits: 30,269
mark.gilbert (4/16/2013)
I love this article, but I'm bothered by the following scenario...

declare @var varchar(7)
set @var = ''

select case when @var NOT LIKE '%[^0-9]%'
then 'I am a number'
else 'I am not a number'
end as [result]

And the result is
result
-----------------
I am a number


Not what I was expecting, but by wrapping the var in a NULLIF function, it works out correctly.


declare @var varchar(7)
set @var = ''

select case when nullif(@var,'') NOT LIKE '%[^0-9]%'
then 'I am a number'
else 'I am not a number'
end as [result]

with the result
result
-----------------
I am not a number


Am I missing something, or is this the exception to the rule?


Thanks for the kudo and the code, Mark.

Shifting gears, you used the word "number" in your code. Let me answer your good question by addressing the concerns of the folks that posted after you. Thanks again for your question.


BWAAA-HAAAA!!!! OK, everyone. Before you continue the great debate as to whether or not an empty string is a "0" or not, let me suggest that you're making a mistake similar to those that have made the mistake of thinking that "IsNumeric" means "IsAllDigits".

The article is NOT about making another version of ISNUMERIC. It's about making a formula to check if something IsAllDigits or, if you wish, about finding a string that has no non-digit characters in it (which an emptry string qualifies for). Empty strings are a special case that require a bit of equally special consideration. Do you want them to be treated as if they were "AllDigits" or not? Technically, empty strings meet the criteria of "IsAllDigits" because there is nothing in them to the contrary and they're not "unknown" as a NULL would be. On a more practical basis for some, they do, in fact, contain nothing, which also means it is known that they contain no digits which can also technically mean that they are not "IsAllDigits".

It's up to the user to decide how to use them. If you want empty strings to be considered to be "IsAllDigits", then use the formula as is. If you want it to be treated as a "0" digit, then use ISNULL/NULLIF to make the translation (but that's a waste of time here because the original formula already treats is as "IsAllDigits"). If you want it (or any fully blank value) to be treated as a NULL, then use NULLIF. Technically (although I do agree that it would be easier in Regex), it's also NOT the fault of LIKE conditions because if you forget to accurately define the same problem in REGEX (what do YOU want an empty string, blank, or NULL to be?), you'll have the same problem! You just need to define the problem of what "IsAllDigits" actually means to you when you come across an empty string.

In any case and while I appreciate the zeal of some of the players on this thread, let's not turn this into an argument similar to what might occur about what a NULL means. Just define what an empty string means to you and, if necessary, modify the formula to accommodate it. It's that easy.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1443079
Posted Wednesday, April 17, 2013 4:11 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:01 PM
Points: 342, Visits: 1,781
Jeff Moden (4/16/2013)

...Technically (although I do agree that it would be easier in Regex), it's also NOT the fault of LIKE conditions because if you forget to accurately define the same problem in REGEX (what do YOU want an empty string, blank, or NULL to be?), you'll have the same problem!...

In any case and while I appreciate the zeal of some of the players on this thread, let's not turn this into an argument similar to what might occur about what a NULL means...


Hi Jeff. Agreed on both. NULL and empty-string are subject to interpretation, although passing back a NULL in both cases might be a way of side-stepping the issue . And my intention was really just to clarify how the single-character range (i.e. [] within a LIKE or PATINDEX) works and to clear up any misunderstanding with regards to the behavior seen by Mark; understanding how [] works will help outside of this particular issue .



Jeff Moden (4/16/2013)
Solomon Rutzky (4/16/2013)
Of course, this is most likely all a moot point given that TRY_PARSE and TRY_CONVERT do a better job (in most cases) of determining Number vs Not a Number .


BWAAA-HAAAA!!!!! It's definitely NOT a moot point for those still using something less than 2012.


A rather valid point again. So to that end I have just added a TryParseToInt function (for TINYINT, SMALLINT, INT, and BIGINT) to SQL# (will be in the next release) so that people using SQL Server 2005 and 2008(R2) will have essentially the same function as TRY_PARSE(). It does accept the Culture name so you can validate "123 456" as a valid INT in French but not English. So at least a bit closer, hopefully, to it all being a moot point .

Take care,
Solomon...





SQL# - http://www.SQLsharp.com/
Post #1443531
« Prev Topic | Next Topic »

Add to briefcase «««1314151617»»

Permissions Expand / Collapse