December 1, 2010 at 7:56 am
Jeff, thank you for writing this article. I've learned a lot by reading your articles and posts.
Somewhere I read that appending '.E0' inside ISNUMERIC would ensure the result is 1 only if all digits were present. I modified steven.malone's query to add a CharEZero column.
--===== Return all characters that ISNUMERIC thinks is numeric
-- (uses values 0-255 from the undocumented spt_Values table
-- instead of a loop from 0-255)
SELECT [Ascii Code] = STR(Number),
[Ascii Character] = CHAR(Number),
[Standalone] = ISNUMERIC(CHAR(Number)),
[CharZero] = ISNUMERIC(CHAR(Number)+'0'),
[ZeroCharZero] = ISNUMERIC('0'+CHAR(Number)+'0'),
[ZeroCharCharZero] = ISNUMERIC('0'+CHAR(Number)+CHAR(Number)+'0'),
[ZeroChar] = ISNUMERIC('0'+CHAR(Number)),
[CharEZero] = ISNUMERIC(CHAR(Number)+'.E0')
FROM Master.dbo.spt_Values
WHERE Type = 'P'
AND Number BETWEEN 0 AND 255
AND (ISNUMERIC(CHAR(Number)) = 1
or 1 = ISNUMERIC(CHAR(Number)+'0')
or 1 = ISNUMERIC('0'+CHAR(Number)+'0')
or 1 = ISNUMERIC('0'+CHAR(Number))
or 1 = ISNUMERIC(CHAR(Number)+'.E0')
)
Here are my results:
Ascii Code Ascii Character Standalone CharZero ZeroCharZero ZeroCharCharZero ZeroChar CharEZero
---------- --------------- ----------- ----------- ------------ ---------------- ----------- -----------
0 0 0 1 1 1 0
9 1 1 0 0 0 0
10 1 1 0 0 0 0
11 1 0 0 0 1 0
12 1 0 0 0 1 0
13 1 1 0 0 0 0
32 0 1 0 0 1 0
36 $ 1 1 0 0 0 0
43 + 1 1 0 0 0 0
44 , 1 1 1 1 1 0
45 - 1 1 0 0 0 0
46 . 1 1 1 0 1 0
48 0 1 1 1 1 1 1
49 1 1 1 1 1 1 1
50 2 1 1 1 1 1 1
51 3 1 1 1 1 1 1
52 4 1 1 1 1 1 1
53 5 1 1 1 1 1 1
54 6 1 1 1 1 1 1
55 7 1 1 1 1 1 1
56 8 1 1 1 1 1 1
57 9 1 1 1 1 1 1
68 D 0 0 1 0 0 0
69 E 0 0 1 0 0 0
92 \ 1 1 0 0 0 0
100 d 0 0 1 0 0 0
101 e 0 0 1 0 0 0
128 € 1 1 0 0 0 0
160 1 0 0 0 1 0
162 ¢ 1 1 0 0 0 0
163 £ 1 1 0 0 0 0
164 ¤ 1 1 0 0 0 0
165 ¥ 1 1 0 0 0 0
101 e 0 0 1 0 0 0
128 € 1 1 0 0 0 0
160 1 0 0 0 1 0
162 ¢ 1 1 0 0 0 0
163 £ 1 1 0 0 0 0
164 ¤ 1 1 0 0 0 0
165 ¥ 1 1 0 0 0 0
December 1, 2010 at 8:11 am
wayne-862477 (12/1/2010)
Jeff,...Realistically the best thing to do would be to create validation by type (IsInt, IsDecimal etc) so you also don't run into overflow problems and things like that.
+1; most practices for more maintainable code have a tremendous overhead relative to what they should have. Some sort of preprocessor macro would be very useful, like #define in C.
Personally, I knew about -, +, ., d, and e, and my workaround for "IS_sort_of_like_an_integer" was to use:
ISNUMERIC(RTRIM(@var)+'.0e0')
The NOT LIKE NOT digits is a cleaner solution for IsDigits, though!
December 1, 2010 at 8:32 am
Sorry if I'm being thick, but at the end of the article, referring to NOT LIKE '%[^0-9]%' you write
The reason why the formula works is that the "^" means "NOT". So the formula is stating "find everything that's not like something that has something not like a numeric digit." Doing the Boolean math, it means "If everything in the string is a digit from 0 to 9, return a 1".
Why not just use LIKE '%[0-9]%'?
That should also mean "If everything in the string is a digit from 0 to 9, return a 1" with out the confusing double negative. Shouldn't it?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 1, 2010 at 9:04 am
Thanks for a great post. Always great to read your stuff:)
I'm curious as to why you made this a table-valued function v. a scalar valued function? Does one perform better than the other?
December 1, 2010 at 9:20 am
Stefan Krzywicki (12/1/2010)
Sorry if I'm being thick, but at the end of the article, referring toNOT LIKE '%[^0-9]%'you writeThe reason why the formula works is that the "^" means "NOT". So the formula is stating "find everything that's not like something that has something not like a numeric digit." Doing the Boolean math, it means "If everything in the string is a digit from 0 to 9, return a 1".
Why not just use
LIKE '%[0-9]%'?That should also mean "If everything in the string is a digit from 0 to 9, return a 1" with out the confusing double negative. Shouldn't it?
Nope, the problem with the LIKE '%[0-9]%' is that it does not mean what you think it does. Instead, it means to find a match if the input string consists of any number of any characters followed by exactly one occurence of the digit 0 through 9 followed by any number of any characters. So, the input 'Bozo1TheClown' will match your LIKE predicate, but it is not numeric, is it?
The bottom line is that Jeff's
NOT LIKE '%[^0-9]%'
is correct. Actually, if you read enough of Jeff's posts you will notice that while he is not an umpire, he is usually never wrong. 😀
Thank you for the article Jeff. It is great, as always. It brings a very good point people sometimes forget: ISNUMERIC was never meant to be a safe check prior to attempting conversion to one of the numeric data types. It can easily return true, but let the conversion fail. This is by design, there is nothing wrong with the way ISNUMERIC works.
Oleg
December 1, 2010 at 9:33 am
Thank you Jeff and I really like the idea of these spackle articles. I'm looking forward to see the others too. 😎
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
December 1, 2010 at 9:40 am
JD Gonzalez (12/1/2010)
Thanks for a great post. Always great to read your stuff:)I'm curious as to why you made this a table-valued function v. a scalar valued function? Does one perform better than the other?
An in-line table-valued function performs much greater than a scalar function or a multi-statement table-valued function. One thing that I'm surprised that Jeff didn't use was the "WITH SCHEMABINDING" for the function - it helps tweak just a little bit more out of it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 1, 2010 at 9:41 am
Excellent article. Thanks for this bit of SQL Spackle.
December 1, 2010 at 9:52 am
Oleg Netchaev (12/1/2010)
Stefan Krzywicki (12/1/2010)
Sorry if I'm being thick, but at the end of the article, referring toNOT LIKE '%[^0-9]%'you writeThe reason why the formula works is that the "^" means "NOT". So the formula is stating "find everything that's not like something that has something not like a numeric digit." Doing the Boolean math, it means "If everything in the string is a digit from 0 to 9, return a 1".
Why not just use
LIKE '%[0-9]%'?That should also mean "If everything in the string is a digit from 0 to 9, return a 1" with out the confusing double negative. Shouldn't it?
Nope, the problem with the LIKE '%[0-9]%' is that it does not mean what you think it does. Instead, it means to find a match if the input string consists of any number of any characters followed by exactly one occurence of the digit 0 through 9 followed by any number of any characters. So, the input 'Bozo1TheClown' will match your LIKE predicate, but it is not numeric, is it?
The bottom line is that Jeff's
NOT LIKE '%[^0-9]%'is correct. Actually, if you read enough of Jeff's posts you will notice that while he is not an umpire, he is usually never wrong. 😀
Oleg
I wasn't saying he was wrong, I was looking for clarification since going for a NOT NOT seems an odd way to go about it. So
NOT LIKE '%[^0-9]%'
means where nothing is not like 0-9
Check every character (that's the %...%)
If it is not like 0-9 (that's the ^)
Then it isn't numeric (that's the NOT LIKE)
If even one character is LIKE instead of NOT LIKE, then don't return the record.
I think part of the confusing aspect is that LIKE with a %...% will return any record with even one character that is like, but NOT LIKE %..% will only return records where not a single character is like. There's 2 things you have to flip in your head when thinking about it.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 1, 2010 at 10:14 am
We use the isReallyNumeric function Sharath posted from http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html as well.
It catches "numbers" with multiple decimal points or multiple negatiion signs, which aren't "real" numeric values.
December 1, 2010 at 10:23 am
Has anyone come up with something that can 'really' tell if a field value is numeric for all of the current sql-server data types?
My original beef with isNumeric() was the single characters that returned 1. For example, a dot is not a number. Period. It's not "numeric". But for me, I *would* like $45,000.25 to return 1. But the IsReallyNumeric() returns 0 for that.
I'm looking for a function with a single argument (the text string) that returns 1 if that value is an actual number in one of the sql server data types. So the problem with trying to get just digits will kick out valid "numbers" that happen to have formatting.
The original issue I'd had was that when trying to deal with dirty data in varchar fields. And the stock IsNumeric() was incorrectly telling me that "." or "$" was a number and therefore I could do math on it, which of course causes problems.
I have seen some functions that require a data type input but that's a no brainer--what I'd really like to see is a function that basically says (and this might hint at how such function might be written) "Can you do math on me?".
--Jim
December 1, 2010 at 10:40 am
Stefan Krzywicki (12/1/2010)
I wasn't saying he was wrong, I was looking for clarification since going for a NOT NOT seems an odd way to go about it. So
NOT LIKE '%[^0-9]%'means where nothing is not like 0-9
Check every character (that's the %...%)
If it is not like 0-9 (that's the ^)
Then it isn't numeric (that's the NOT LIKE)
If even one character is LIKE instead of NOT LIKE, then don't return the record.
I think part of the confusing aspect is that LIKE with a %...% will return any record with even one character that is like, but NOT LIKE %..% will only return records where not a single character is like. There's 2 things you have to flip in your head when thinking about it.
All I was trying to point out was that your original suggestion to use LIKE '%[0-9]%' instead is not going to work, that is all.
-- Jeff's
NOT LIKE '%[^0-9]%'
-- is not equivalent to your
LIKE '%[0-9]%'
These are totally different predicates. The former by Jeff correctly matches the input values consisting of any number of any digits.
The latter you suggested does not do the same. It does not have double negatives, but does not really work either, because it matches input values consisting of any number of any characters as long as the input includes one digit (actually one or more because every next digit also satisfies any character pattern)
Oleg
December 1, 2010 at 10:47 am
James Stephens (12/1/2010)
Has anyone come up with something that can 'really' tell if a field value is numeric for all of the current sql-server data types?My original beef with isNumeric() was the single characters that returned 1. For example, a dot is not a number. Period. It's not "numeric". But for me, I *would* like $45,000.25 to return 1. But the IsReallyNumeric() returns 0 for that.
<snip>
I would suggest changing the isReallyNumeric() function to use the isNumeric() function on the first character. 🙂
Instead of this block at the beginning:
IF LEFT(@num, 1) = '-'
SET @num = SUBSTRING(@num, 2, LEN(@num))
use this:
IF ISNUMERIC(LEFT(@num, 1))
SET @num = SUBSTRING(@num, 2, LEN(@num))
You could also add ',' to the PATINDEX to allow your example of '$42,500.25' to pass.
My version of the IsReallyNumeric() looks like this, but for safety, you need to cast the input as MONEY to do math with it.
SELECT @num = LTRIM(RTRIM(@num))
IF ISNUMERIC(LEFT(@num, 1)) = 1
SET @num = SUBSTRING(@num, 2, LEN(@num))
DECLARE @pos TINYINT
SET @pos = 1 + LEN(@num) - CHARINDEX('.', REVERSE(@num))
RETURN CASE
WHEN PATINDEX('%[^0-9.,-]%', @num) = 0
AND @num NOT IN ('.', '-', ',', '+', '^')
AND LEN(@num)>0
AND @num NOT LIKE '%-%'
AND
(
((@pos = LEN(@num)+1)
OR @pos = CHARINDEX('.', @num))
)
THEN
1
ELSE
0
END
December 1, 2010 at 10:47 am
Wow! Great discussions going on here, folks! I'm at work right now so can't respond in detail. I'll have to wait until tonight. In the meantime, thank all of you who are participating in this discussion. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
December 1, 2010 at 10:50 am
James Stephens (12/1/2010)
My original beef with isNumeric() was the single characters that returned 1. For example, a dot is not a number. Period. It's not "numeric". But for me, I *would* like $45,000.25 to return 1. But the IsReallyNumeric() returns 0 for that.
If that function is doing what I think it's doing, it's properly returning 0. By the standards of the function, $45,000.25 is not a numeric value, but a monetary / currency value.
But you could do IsReallyNumeric(REPLACE(REPLACE(MyValue,',',''),'$','')) to find if your monetary values are numeric.
Viewing 15 posts - 31 through 45 (of 168 total)
You must be logged in to reply to this topic. Login to reply