December 6, 2010 at 1:25 pm
Thanks Jeff. Good stuff ... as usual.
I guess this makes me a lurker no more.
December 6, 2010 at 2:47 pm
Paul Herbert (12/6/2010)
I guess this makes me a lurker no more.
Ladies and Gentlemen... meet Paul Herbert. He's the new Systems DBA where I work. We've only been working together for several months but we've slain several large dragons together and he's killed a few big ones on his own!. Paul was becoming a frequent lurker and has finally decided to test the bath water here at SSC.
Paul, welcome to SQL Server Central.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 6, 2010 at 3:45 pm
Paul Herbert (12/6/2010)
Thanks Jeff. Good stuff ... as usual.I guess this makes me a lurker no more.
Hey Paul, welcome to the club.
Your brother Frank does nice work. Jeff mentions dragons, but wasn't that mostly about worms? 😉
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 7, 2010 at 4:16 am
Really . . . I don't know where to start. I suppose the thing to say is having command of data types is an essential for any coder. ISNUMERIC functions perfectly. Every example given is, in fact, a valid numeric.
"E" and "D" both denote scientific notation . . . would one actually expect the alternative to the example "12e34", which is 12 followed by 34 zeros, to be practical?! Search BOL for "scientific notation" to acquaint one's self with REAL, FLOAT, Constants, CAST/CONVERT, EXP . . . same goes for MONEY with respect to its valid formatting.
This article is an example of how poor coding propagates. But, let's assume the answer is (partially) correct and ask the question to the answer. "How to determine if a value is an integer?"
SELECTPATINDEX('%[^0-9]%','<whatever value>')
Only zero indicates the value is an integer.
December 7, 2010 at 4:30 am
Mike McIver (12/7/2010)
This article is an example of how poor coding propagates.
I have to disagree.
Yes, you are right - all the examples are indeed valid string representations of a value in "some" numeric data type. But there's no way to find which data type.
The only situations where I have yet seen people use functions such as ISNUMERIC, is for parsing input from a potentially erroneous source - like user input, or when parsing data sent from some third party outside the company's control. The data is expected to be in some specific numeric format, and to prevent runt-time erros, ISNUMERIC is used in an attempt to catch invalid data.
Except it fails. "12e34" being a valid floating point number is interesting, but totally irrelevant when my task is to prevent run-time errors when storing user input in a decimal(24,7) column.
The code presented in this article does not fill that gap - but it does provide a short and simple way to at least test data that has to be converted to an integer. It's far from ideal, but until Microsoft implements an IS_VALID_CONVERT function, or a set of ISINT, ISFLOAT, ISDECIMAL, ISMONEY, etc functions, we'll have to make do.
December 7, 2010 at 5:58 am
ISNUMERIC functions perfectly. Every example given is, in fact, a valid numeric.
Ummm... I think you missed where we discussed two commas in a row with no numbers
select isnumeric(',,')
-----------
1
(1 row(s) affected)
If that is "working perfectly" please supply your definition of perfect.
Go back and look at the results I posted on 12/1/2010 12:38:45 PM for many more examples
December 7, 2010 at 6:04 am
Mike McIver (12/7/2010)
SELECTPATINDEX('%[^0-9]%','<whatever value>')Only zero indicates the value is an integer.
Not true. Integers can be negative numbers.
Dictionary.com
in·te·ger /'?nt?d??r/ Show Spelled[in-ti-jer] Show IPA
–noun
1. Mathematics . one of the positive or negative numbers 1, 2, 3, etc., or zero.
SELECT PATINDEX('%[^0-9]%','-1') fails your test. By your standards, -1 should return a zero. It doesn't. It returns a 1.
December 7, 2010 at 6:34 am
steven.malone (12/7/2010)
ISNUMERIC functions perfectly. Every example given is, in fact, a valid numeric.
Ummm... I think you missed where we discussed two commas in a row with no numbers
select isnumeric(',,')-----------
1
(1 row(s) affected)
If that is "working perfectly" please supply your definition of perfect.
Go back and look at the results I posted on 12/1/2010 12:38:45 PM for many more examples
The string constant ',,' can be converted to the money data type. So ',,' can be converted to a numeric data type, and ISNUMERIC is correct in returning 1.
Of course, this is in fact quite useless, but I already explained that in my previous reply to Steven.
December 7, 2010 at 7:41 am
Mike McIver (12/7/2010)
Really . . . I don't know where to start. I suppose the thing to say is having command of data types is an essential for any coder. ISNUMERIC functions perfectly. Every example given is, in fact, a valid numeric."E" and "D" both denote scientific notation . . . would one actually expect the alternative to the example "12e34", which is 12 followed by 34 zeros, to be practical?! Search BOL for "scientific notation" to acquaint one's self with REAL, FLOAT, Constants, CAST/CONVERT, EXP . . . same goes for MONEY with respect to its valid formatting.
This article is an example of how poor coding propagates. But, let's assume the answer is (partially) correct and ask the question to the answer. "How to determine if a value is an integer?"
SELECTPATINDEX('%[^0-9]%','<whatever value>')
Only zero indicates the value is an integer.
IsNumeric functions "perfectly" in that it does what it is supposed to do. Unfortunately, that's not what most people want it to do. Most of the time if you come across 12e34 in your data, it isn't a 12 followed by 34 zeroes, it is a typo. IsNumeric doesn't help in this situation at all. I've never worked on a project where scientific notation was expected or wanted. This isn't to say such situations don't exist, just that they're not common.
--------------------------------------
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 7, 2010 at 8:16 am
The string constant ',,' can be converted to the money data type
I stand corrected. I had tried a variety of numeric types, but not money.
I had not realized how forgiving the conversion to money can be.
A little experimentation shows that most of the results that look like "false positives" can be explained by money. Hmmm.... perhaps convert(int,convert(money,<iffy string where isnumeric = 1>)) will be useful, for example convert(int,convert(money,'2,,3'))
I was still wondering about '0'+char(0)+char(0)+'0' but to be sure I wanted to check every type:
select 'select '''+name+''' NumType, convert('+name+',''0''+char(0)+char(0)+''0'') Converted
'+'go
'
from sys.types where precision > 0
Sure enough, it will convert to float or real
I learned something new today, thanks.
December 7, 2010 at 9:18 am
steven.malone (12/7/2010)
A little experimentation shows that most of the results that look like "false positives" can be explained by money.
The majority of the ones that won't convert to money will convert to float. Like
select isnumeric('1d4')
select cast('1d4' as money)
select cast('1d4' as float)
(no, it doesn't return a value between 1 and 4 inclusive)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 7, 2010 at 9:36 am
It shows how America-centric SQL programmers are.
1d4 means One Pound, Four Pence in England.
December 7, 2010 at 9:48 am
steven.malone (12/7/2010)
It shows how America-centric SQL programmers are.1d4 means One Pound, Four Pence in England.
And the irony there is that Gail's from South Africa... 🙂
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 7, 2010 at 9:54 am
Gail,
Am I supposed to be getting an error on the Money conversion part of your code?
December 7, 2010 at 9:59 am
Craig Farrell (12/7/2010)
steven.malone (12/7/2010)
It shows how America-centric SQL programmers are.1d4 means One Pound, Four Pence in England.
And the irony there is that Gail's from South Africa... 🙂
And a British citizen to boot.
1d4 means, to someone familiar with tabletop roleplaying, roll a 4-sided dice once. (5d6 would mean roll a 6 sided dice 5 times)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 76 through 90 (of 168 total)
You must be logged in to reply to this topic. Login to reply