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, November 26, 2013 10:20 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 4:51 AM
Points: 9,928, Visits: 11,199
I've just noticed that "IsAllDigits" includes fractions:

-- True
SELECT IsAllDigits
FROM dbo.IsAllDigits('1¼');

-- True
SELECT 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 White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1517898
Posted Tuesday, November 26, 2013 10:28 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:22 AM
Points: 841, Visits: 1,474
Paul White (11/26/2013)
I've just noticed that "IsAllDigits" includes fractions:

-- True
SELECT IsAllDigits
FROM dbo.IsAllDigits('1¼');

-- True
SELECT 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

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 4:51 AM
Points: 9,928, Visits: 11,199
twin.devil (11/26/2013)
that could be the QotD mate

Ha! I missed an opportunity there.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1517905
Posted Wednesday, November 27, 2013 7:24 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 2:42 PM
Points: 35,612, Visits: 32,208
Paul White (11/26/2013)
I've just noticed that "IsAllDigits" includes fractions:

-- True
SELECT IsAllDigits
FROM dbo.IsAllDigits('1¼');

-- True
SELECT 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."

(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 #1518077
Posted Wednesday, November 27, 2013 7:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 7,874, Visits: 9,613
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-Dedicated

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

Group: General Forum Members
Last Login: Today @ 2:42 PM
Points: 35,612, Visits: 32,208
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."

(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 #1518098
Posted Wednesday, November 27, 2013 10:49 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 2:42 PM
Points: 35,612, Visits: 32,208
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."

(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 #1518152
Posted Wednesday, November 27, 2013 6:18 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 4:51 AM
Points: 9,928, Visits: 11,199
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1518240
Posted Wednesday, November 27, 2013 7:12 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 2:42 PM
Points: 35,612, Visits: 32,208
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."

(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 #1518244
« Prev Topic | Next Topic »

Add to briefcase «««1314151617

Permissions Expand / Collapse