Why doesn’t ISNUMERIC work correctly? (SQL Spackle)

Question

Post reply

Why doesn’t ISNUMERIC work correctly? (SQL Spackle)

Jeff Moden

SSC Guru

Points: 1004339
More actions
November 30, 2010 at 9:00 pm

#231759

Comments posted to this topic are about the item Why doesn’t ISNUMERIC work correctly? (SQL Spackle)
--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.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 168 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

Great spackle Jeff! Thanks for putting this brief article together - an easy and informative read.

cheers,

Steve.

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 2

Simple and nice article,

some time back i was working on validating a column in the database and i came across this issue and we had this function to validate.

CREATE FUNCTION dbo.isReallyNumeric

(

@num VARCHAR(64)

)

RETURNS BIT

BEGIN

IF LEFT(@num, 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

GO

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Good stuff Jeff. Got that spackle article out pretty quick.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

autoexcrement SSCertifiable Points: 5956 More actions · Answer 4

sharath.chalamgari (11/30/2010)
Simple and nice article,
some time back i was working on validating a column in the database and i came across this issue and we had this function to validate.

This poster brings up a good point: "-" and ".".

"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

stevefromOZ (11/30/2010)
Great spackle Jeff! Thanks for putting this brief article together - an easy and informative read.
cheers,

Thanks for the positive feedback, Steve. I called these short articles "SQL Spackle" for the reason given. The concept of these short, single point, get to the point articles was Steve Jone's idea. I was a little worried that some folks would look at these a snub a nose with "Pffft! Already knew that and the article is too short!". That's why we had Phil McCracken (pen-name suggested by Paul White) do a lead in on each of these articles. To make them easy to find, Steve created a new keyword lookup for the word "spackle".

Again, thanks for taking the time to provide your feedback.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

sharath.chalamgari (11/30/2010)
Simple and nice article,
some time back i was working on validating a column in the database and i came across this issue and we had this function to validate.

Thanks for the feedback and the code example, Sharath. That's one of the really good things about having the "discussion area" even on short articles. Goodies like what you posted come right out.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

autoexcrement (11/30/2010)
sharath.chalamgari (11/30/2010)
Simple and nice article,
some time back i was working on validating a column in the database and i came across this issue and we had this function to validate.
This poster brings up a good point: "-" and ".".

If you're looking for valid numbers, I agree. If you're looking for an "IsAllDigits" function, you wouldn't want to include those two characters.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

CirquedeSQLeil (11/30/2010)
Good stuff Jeff. Got that spackle article out pretty quick.

Thanks, Jason. Heh... When Steve first came out with the "Requested Articles" forum, I banged out 2 "spackles" that night and 2 more the next night. From the looks of the expected publish dates he's assigned on my contribution page, it looks like he has me scheduled every Wednesday for about 4 weeks. The first one came out on Nov 15th.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 9

Jeff Moden (11/30/2010)
autoexcrement (11/30/2010)
sharath.chalamgari (11/30/2010)
Simple and nice article,
some time back i was working on validating a column in the database and i came across this issue and we had this function to validate.
This poster brings up a good point: "-" and ".".
If you're looking for valid numbers, I agree. If you're looking for an "IsAllDigits" function, you wouldn't want to include those two characters.

yes we were looking at the valid numbers and used this function.

i got this function from the below URL

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html">

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Jeff Moden (11/30/2010)
CirquedeSQLeil (11/30/2010)
Good stuff Jeff. Got that spackle article out pretty quick.
Thanks, Jason. Heh... When Steve first came out with the "Requested Articles" forum, I banged out 2 "spackles" that night and 2 more the next night. From the looks of the expected publish dates he's assigned on my contribution page, it looks like he has me scheduled every Wednesday for about 4 weeks. The first one came out on Nov 15th.

I must have missed it. What is the link?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

mohd.nizamuddin Ten Centuries Points: 1328 More actions · Answer 11

Thanks Jeff for this nice and very well explained article.

hpoe SSC Journeyman Points: 96 More actions · Answer 12

autoexcrement (11/30/2010)
sharath.chalamgari (11/30/2010)
Simple and nice article,
some time back i was working on validating a column in the database and i came across this issue and we had this function to validate.
This poster brings up a good point: "-" and ".".

I have used a similar function but I had to make sure that the string could be converted into a float.

So there must be additional checks:

"-" and "+" only at the first position

"." allowed only once

TestCases:

select cast('+1.0'as float) -- OK

select cast('-1.0'as float) -- OK

select cast('1.0-'as float) -- NOK

select cast('1.0+'as float) -- NOK

select cast('.5' as float) -- OK

select cast('5.' as float) -- OK

select cast('5.5.5' as float) -- NOK

DavidBridgeTechnology.com SSChasing Mays Points: 649 More actions · Answer 13

Very nice article. Well written and now added to my bookmarks.

Thanks for sharing

David Bridge

http://www.DavidBridgeTechnology.com

David Bridge
David Bridge Technology Limited
www.davidbridgetechnology.com

Brandie Tarvin SSC Guru Points: 173059 More actions · Answer 14

Even people who think they know should read this article. I actually knew all about the punctuation and currency signs, but then I got to the "e" and "d" part. DOH. Hadn't run into that issue before. Mentioning scientific notation = Good Idea.

Great article. Thumbs up.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.