Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Why doesn’t ISNUMERIC work correctly? (SQL Spackle) Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, November 26, 2013 10:20 PM
 SSCrazy Eights Group: General Forum Members Last Login: 2 days ago @ 6:42 AM Points: 9,932, Visits: 11,347
 I've just noticed that "IsAllDigits" includes fractions:`-- TrueSELECT IsAllDigits FROM dbo.IsAllDigits('1¼');-- TrueSELECT IsAllDigits FROM dbo.IsAllDigits('¾5¼');`Collation-related of course. There is more between 0 and 9 than just the integers. In the US default collation SQL_Latin1_General_CP1_CI_AI:`² (superscript 2)³ (superscript 3)¹ (superscript 1)¼½¾` Paul WhiteSQLPerformance.comSQLblog.com@SQL_Kiwi
Post #1517898
 Posted Tuesday, November 26, 2013 10:28 PM
 Ten Centuries Group: General Forum Members Last Login: Today @ 6:32 AM Points: 1,342, Visits: 2,565
 Paul White (11/26/2013)I've just noticed that "IsAllDigits" includes fractions:`-- TrueSELECT IsAllDigits FROM dbo.IsAllDigits('1¼');-- TrueSELECT IsAllDigits FROM dbo.IsAllDigits('¾5¼');`Collation-related of course. There is more between 0 and 9 than just the integers. In the US default collation SQL_Latin1_General_CP1_CI_AI:`² (superscript 2)³ (superscript 3)¹ (superscript 1)¼½¾`that could be the QotD mate
Post #1517901
 Posted Tuesday, November 26, 2013 10:36 PM
 SSCrazy Eights Group: General Forum Members Last Login: 2 days ago @ 6:42 AM Points: 9,932, Visits: 11,347
 twin.devil (11/26/2013)that could be the QotD mate Ha! I missed an opportunity there. Paul WhiteSQLPerformance.comSQLblog.com@SQL_Kiwi
Post #1517905
 Posted Wednesday, November 27, 2013 7:24 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 2:41 PM Points: 42,081, Visits: 39,473
 Paul White (11/26/2013)I've just noticed that "IsAllDigits" includes fractions:`-- TrueSELECT IsAllDigits FROM dbo.IsAllDigits('1¼');-- TrueSELECT IsAllDigits FROM dbo.IsAllDigits('¾5¼');`Collation-related of course. There is more between 0 and 9 than just the integers. In the US default collation SQL_Latin1_General_CP1_CI_AI:`² (superscript 2)³ (superscript 3)¹ (superscript 1)¼½¾`Thanks for the headsup on this, Paul.When I installed 2005, I used the default US collation and it was SQL_Latin1_General_CP1_CI_AS. I just checked my 2008 installation where I also accepted the default and it's also SQL_Latin1_General_CP1_CI_AS. I'm pretty sure that's the actual US Default Collation.Also, when I run the code you posted, they both return "0" even if I force the collation to be SQL_Latin1_General_CP1_CI_AI using COLLATE both internal to the function and externallly. Is there something else or some other collation that you may have been using?What do you have for a default collation on the server that you tested the code with? I'd like to give it a try with that. Thanks. --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." Helpful Links:How to post code problemsHow to post performance problems
Post #1518077
 Posted Wednesday, November 27, 2013 7:59 AM
 SSCrazy Eights Group: General Forum Members Last Login: Today @ 2:14 PM Points: 9,833, Visits: 11,907
 In SQL 2012 the fractions are between 0 and 9 in Latin1_General_CI_AS but not in SQL_Latin1_General_CP1_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.edit: Oops, I'd missed out CP1_ from the SQL collation name. So I've added it. Tom
Post #1518087
 Posted Wednesday, November 27, 2013 8:32 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 2:41 PM Points: 42,081, Visits: 39,473
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1518098
 Posted Wednesday, November 27, 2013 10:49 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 2:41 PM Points: 42,081, Visits: 39,473
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1518152
 Posted Wednesday, November 27, 2013 6:18 PM
 SSCrazy Eights Group: General Forum Members Last Login: 2 days ago @ 6:42 AM Points: 9,932, Visits: 11,347
 Jeff Moden (11/27/2013)When I installed 2005, I used the default US collation and it was SQL_Latin1_General_CP1_CI_ASAh - 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 WhiteSQLPerformance.comSQLblog.com@SQL_Kiwi
Post #1518240
 Posted Wednesday, November 27, 2013 7:12 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 2:41 PM Points: 42,081, Visits: 39,473
 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 correctlySQL_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." Helpful Links:How to post code problemsHow to post performance problems
Post #1518244

 Permissions