SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)

Group: General Forum Members
Points: 430456 Visits: 43457
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
stevefromOZ
stevefromOZ
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: Moderators
Points: 19892 Visits: 3757
Great spackle Jeff! Thanks for putting this brief article together - an easy and informative read.

cheers,

Steve.
sharath.chalamgari
sharath.chalamgari
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2938 Visits: 798
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
END
GO



SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)

Group: General Forum Members
Points: 123996 Visits: 18627
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

autoexcrement
autoexcrement
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2185 Visits: 915
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
Jeff Moden
SSC Guru
SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)

Group: General Forum Members
Points: 430456 Visits: 43457
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)

Group: General Forum Members
Points: 430456 Visits: 43457
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)

Group: General Forum Members
Points: 430456 Visits: 43457
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)

Group: General Forum Members
Points: 430456 Visits: 43457
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sharath.chalamgari
sharath.chalamgari
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2938 Visits: 798
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search