December 1, 2010 at 4:29 pm
I have some already seen several posts about how useless ISNUMERIC() is, and how SQL Server lacks a function that tests is a value can actually be cast to a specified numeric data type before attempting the conversion. But I don't think I have yet seen a reference to the suggestion made by MVP Erland Sommarskog on Connect to add an IS_VALID_CONVERT() function to SQL Server.
Make yourself heard - vote at https://connect.microsoft.com/SQLServer/feedback/details/354766/add-a-is-valid-convert-function
December 1, 2010 at 5:43 pm
The function as written also returns 1 for the empty string ''.
December 2, 2010 at 7:14 am
Holy moly... 61 posts. I've read them all and I'll try to summarize...
First, thank all of you again for the high quality level of discussion. This one has been a real pleasure thanks to all of you good folks whether you were asking a question or answering one.
Second... thank all of you that answered other's questions. You just have to love this community. The only question that I saw that was really unanswered was the one where someone asked something like "What about things like 0E0"? The answer to that question is that was specifically covered in the article and although ISNUMERIC correctly identifies it as a convertable number, the function I wrote correctly say it's not all digits.
One of the posts correctly stated that if the string is an empty string, that it returns a 1. I guess it's up to you as to whether or not that's a correct output. However, here's a slightly modified version that will return a 0 for an empty string...
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.
This one returns a "0" if the string parameter is blank, and empty
string, or null.
--Jeff Moden
********************************************************************/
--===== Declare the I/O parameters
(@MyString VARCHAR(8000))
RETURNS TABLE AS
RETURN (
SELECT CASE
WHEN NULLIF(@MyString,'') NOT LIKE '%[^0-9]%'
THEN 1
ELSE 0
END AS IsAllDigits
)
I appreciate all of the good folks that posted their code examples for expansion on the principle of this article so that you can check for things like correct positioning of negative signs and the like. That was one of the purposes of this series of articles... get the creative juices flowing and a chance for others to show what they've done. Well done!
There were some people that implied the article failed to cover all the checks that could be made. Heh... it didn't fail in that area because, as stated at the beginning of the article, it wasn't mean to provide a complete solution and none of thse "spackle" articles are. They're meant to say "Here's a tool... It's called an 'adjustable wrench'. Here's how you adjust the jaws on the tool. Here's how to tighten a nut with it." And that's it. It's up to you to figure out that the tool can also be used as a small vise to hold small parts, can be used as a kind of back scratcher, and can be used as an emergency hammer for small stuff. 😛 And when you figure out that different use, hopefully you'll post it for others to learn from. 😉
Last but not least, thank all of you good folks that provided feedback, especially the positive feedback. It's the feedback (as well as the helpful discussions) that encourages other people to keep this community going and the articles coming.
Well done one and all and thank you for all that you do in this community. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
December 2, 2010 at 7:22 am
crashdan (12/1/2010)
please also consider 0E0 and numbers like them.
Good point but that's actually and explicity covered in the article. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
December 2, 2010 at 7:31 am
steven.malone (12/1/2010)
First off thanks, like others I had not stopped to think about "d" and "e" and I am a little surprised "x" was not included if they were. go figure.However, when I ran your script on my machine the returned data set included 92 \ (backslash).
Is there some setting that affects that?
I expanded your script a little:
--===== 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))
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))
)
And got this result set:
Ascii Code Character Standalone CharZero ZeroCharZero ZeroCharCharZero ZeroChar---------- --------- ----------- ----------- ------------ ---------------- -----------
0 0 0 1 1 1
9 1 1 0 0 0
10 1 1 0 0 0
11 1 0 0 0 1
12 1 0 0 0 1
13 1 1 0 0 0
32 0 1 0 0 1
36 $ 1 1 0 0 0
43 + 1 1 0 0 0
44 , 1 1 1 1 1
45 - 1 1 0 0 0
46 . 1 1 1 0 1
48 0 1 1 1 1 1
49 1 1 1 1 1 1
50 2 1 1 1 1 1
51 3 1 1 1 1 1
52 4 1 1 1 1 1
53 5 1 1 1 1 1
54 6 1 1 1 1 1
55 7 1 1 1 1 1
56 8 1 1 1 1 1
57 9 1 1 1 1 1
68 D 0 0 1 0 0
69 E 0 0 1 0 0
92 \ 1 1 0 0 0
100 d 0 0 1 0 0
101 e 0 0 1 0 0
128 € 1 1 0 0 0
160 1 0 0 0 1
162 ¢ 1 1 0 0 0
163 £ 1 1 0 0 0
164 ¤ 1 1 0 0 0
165 ¥ 1 1 0 0 0
Notice that 44 (comma) has a one in the ZeroCharCharZero column.
Also that 0 has ones in the last three columns.
I tried and
select convert(numeric,'0,,0')go
select convert(numeric,'0'+char(0)+'0')
go
select convert(numeric,'0'+char(0)+char(0)+'0')
go
select convert(numeric,'0'+char(0))
go
all throw a errors
Bottom line is from now on if I need to validate input I think I will put in a try block assigning the string to a variable of the actual type I need and if I need to validate columns use a type specific function.
Again, thanks for the food for thougt.
Thank you for the catch. I'm not sure why the copy'n'paste I did didn't show up in the article other than (much like in some of our posts) that some characters cause a wierd effect on the post. The backslash does show up in the grid from my original code as having a "1' for ISNUMERIC. Although it doesn't work as a mathematical operator in SQL Server, some languages use the backslash to indicate "integer division" and is the logical opposite of a modulo operator.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 2, 2010 at 7:41 am
Steve Jones - SSC Editor (12/1/2010)
Excellent article. Thanks for this bit of SQL Spackle.
Thanks Steve but the real thanks goes to you. You had a wonderful idea when you suggested that some smaller, single point articles were needed to answer some of the more common questions. Thanks for suggesting that we needed to fill in some of the cracks. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
December 2, 2010 at 10:28 am
Jeff Moden (12/2/2010)
Steve Jones - SSC Editor (12/1/2010)
Excellent article. Thanks for this bit of SQL Spackle.Thanks Steve but the real thanks goes to you. You had a wonderful idea when you suggested that some smaller, single point articles were needed to answer some of the more common questions. Thanks for suggesting that we needed to fill in some of the cracks. 🙂
Correct. If it wasn't such a good idea, there wouldn't have been dozens of folks jumping in to help out with this project. Great idea Steve, and thank you for it!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 2, 2010 at 11:04 am
Honestly, I think the #1 takeaway from this thread is:
Name functions with accuracy and specificity!
Microsoft's "IsNumeric" is neither specific, nor accurate (it does _not_ test for strings that must match the NUMERIC type... and doesn't specify the NUMERIC type vs. the general linguistic definition of "numeric").
Jeff's IsAllDigits is quite good, though for true accuracy and specificity it would have to be something like either IsAllDigitsOrEmptyString or IsAllDigitsLengthAtLeastOne. Note that neither of these make any pretense about whether a value can be converted to a given type or not, which is accurate (maybe it's a VARCHAR(MAX) filled with digits!)
CanBeConvertedToInt, CanBeConvertedToCurrency, etc. would be good names, as well.
This is similar to Age calculation functions: Age(birthdate, asofdate) is a bad name for calculating age. AgeInMonths(birthdate, asofdate) is better, specifying the output unit. AgeInYears_LeapDayFeb28(birthdate, asofdate) is better still, also specifying what leap days default to in non-leap-years.
When we have a more than one NUMERIC type, specifying which we're talking about, if any, becomes critical.
December 2, 2010 at 11:12 am
Nadrek (12/2/2010)
Honestly, I think the #1 takeaway from this thread is:Name functions with accuracy and specificity!
This is similar to Age calculation functions: Age(birthdate, asofdate) is a bad name for calculating age. AgeInMonths(birthdate, asofdate) is better, specifying the output unit. AgeInYears_LeapDayFeb28(birthdate, asofdate) is better still, also specifying what leap days default to in non-leap-years.
Perhaps, but there's a reasonable limit to what I want to type in constantly. The auto-complete function in SSMS gets in my way more often then helps to the point I deactivated it completely. So, Age/AgeInMonths... yeah, okay. AgeInYears_LeapDayFeb28... not so much if it's regularly used. I can (almost) always pop open the function in question and remind myself if necessary.
Does this help new coders to the project? No, not really, and you're right, that function is explicit and saves them time reading it later.
Then again, we're Database folks. A 5% write level (1 write, read 19 times) for data is HIGH, 25% outrageous (written once, read 3 times). How often do you re-write old code, except for a few specific core procs on any system?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 2, 2010 at 11:53 am
Craig Farrell (12/2/2010)
How often do you re-write old code, except for a few specific core procs on any system?
Often enough that I save all my procs so I can re-use what's already written instead of rewriting it again.
Of course, in my environment, we answer a lot of Ad Hoc "Why did this happen" questions, which are always based off the same tables.
December 2, 2010 at 1:03 pm
crashdan (12/1/2010)
please also consider 0E0 and numbers like them.
Wasn't that covered in the Old_MacDonald() Function? Or was that the Flying_Monkeys() I'm thinking of?
Sorry...couldn't help it. 🙂
December 2, 2010 at 1:38 pm
James Stephens (12/2/2010)
crashdan (12/1/2010)
please also consider 0E0 and numbers like them.Wasn't that covered in the Old_MacDonald() Function? Or was that the Flying_Monkeys() I'm thinking of?
Sorry...couldn't help it. 🙂
It was Flying_Monkeys()
Old_MacDonald is an EIE IO.
--------------------------------------
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 2, 2010 at 2:48 pm
Thanks Jeff! That is really helpful!
Cheers,
Nicole Bowman
Nothing is forever.
December 2, 2010 at 10:07 pm
James Stephens (12/2/2010)
crashdan (12/1/2010)
please also consider 0E0 and numbers like them.Wasn't that covered in the Old_MacDonald() Function? Or was that the Flying_Monkeys() I'm thinking of?
Sorry...couldn't help it. 🙂
Heh... too funny... but now I can't get that darned song out of my head. YOEO! YEOW-OWE!
--Jeff Moden
Change is inevitable... Change for the better is not.
December 2, 2010 at 10:08 pm
Nicole Bowman (12/2/2010)
Thanks Jeff! That is really helpful!Cheers,
Thanks for the feedback and for stopping by, Nicole. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 61 through 75 (of 168 total)
You must be logged in to reply to this topic. Login to reply