Why doesn’t ISNUMERIC work correctly? (SQL Spackle)

Question

Why doesn’t ISNUMERIC work correctly? (SQL Spackle)

Viewing 4 posts - 166 through 168 (of 168 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 1

L' Eomot Inversé (11/27/2013)
In SQL 2012 the fractions are between 0 and 9 in Latin1_General_CI_AS but not in SQL_Latin1_General_CI_AS. It's the same in SQL 2008 R2, and unless my memory is broken also in SQL 2008. I imagine it would have been the same in SQL 2005 but I didn't use it enough to find out. I'm not certain what the situation was in SQL 2000, but I think it was probably the same - I used it enough to make it likely that I would have noticed if this had changed when I moved to SQL 2008 or 2008 R2.

Thanks, Tom. I'll give it a try.

--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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 2

Jeff Moden (11/27/2013)
L' Eomot Inversé (11/27/2013)
In SQL 2012 the fractions are between 0 and 9 in Latin1_General_CI_AS but not in SQL_Latin1_General_CI_AS. It's the same in SQL 2008 R2, and unless my memory is broken also in SQL 2008. I imagine it would have been the same in SQL 2005 but I didn't use it enough to find out. I'm not certain what the situation was in SQL 2000, but I think it was probably the same - I used it enough to make it likely that I would have noticed if this had changed when I moved to SQL 2008 or 2008 R2.
Thanks, Tom. I'll give it a try.

Confirmed in 2005 and 2008. I haven't installed 2012, yet. The problem of fraction symbols being recognized as valid digits occurs for the Latin1_General_CI_AS collation but it does not occur for the US install default of SQL_Latin1_General_CP1_CI_AS nor does it occur for SQL_Latin1_General_CP1_CI_AI.

This yet another place where a collation of Latin1_General_Bin in the function itself would keep anyone from having problems.

--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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 3

Jeff Moden (11/27/2013)
When I installed 2005, I used the default US collation and it was SQL_Latin1_General_CP1_CI_AS

Ah - I had forgotten the default US collation was accent sensitive. Anyway, it's interesting. One might think NOT LIKE '%[^0123456789]%' would be enough, but many collations put the superscript 1,2,3 in that set, although it does omit the fractions. Using a specific collation that includes only the Latin numerics in the function seems safest, I agree.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 4

Hey folks, just in case you've gotten lost somewhere along the line on this last bit about collation, allow me to summarize...

Paul found, Tom confirmed, and I tested that certain collations allow fractions and superscript characters to be recognized as a numeric digit by the NOT LIKE '%[^0123456789]%' portion of the IsAllDigits function. You'll have to test your own collation but here are some that we've covered. I'm bringing these up because they're very similarly named (except for the "BIN" one), 1 is the US installation default, and that works correctly.

Collation Name IsAllDigits Function Test Comments

---------------------------- -------------------------------------------------------

SQL_Latin1_General_CP1_CI_AI - Not the US default but works correctly

SQL_Latin1_General_CP1_CI_AS - US default and works correctly

Latin1_General_CI_AS - AU default and doesn't work correctly

Latin1_General_Bin - Also works correctly and is usually one of the fastest.

Note that "Latin1_General_CI_AS" (the one that doesn't work with fractions/superscripts) is the default for most "English" locals according to Books Online so this affects a whole lot of people.

The fix is fairly simple, though. Just modify the function to use the Latin1_General_Bin collation like this...

CREATE FUNCTION dbo.IsAllDigits

/********************************************************************

Purpose:

This function will return a 1 if the string parameter contains only

numeric digits and will return a 0 in all other cases. Use it in

a FROM clause along with CROSS APPLY when used against a table.

--Jeff Moden

********************************************************************/

--===== Declare the I/O parameters

(@MyString VARCHAR(8000))

RETURNS TABLE AS

RETURN (

SELECT CASE

WHEN @MyString COLLATE Latin1_General_BIN NOT LIKE '%[^0-9]%' COLLATE Latin1_General_BIN

THEN 1

ELSE 0

END AS IsAllDigits

)

;

--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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)