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

)

;