Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Why doesn’t ISNUMERIC work correctly? (SQL Spackle) Expand / Collapse
Author
Message
Posted Tuesday, November 30, 2010 9:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 35,372, Visits: 31,919
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1028368
Posted Tuesday, November 30, 2010 9:27 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Wednesday, October 22, 2014 10:02 AM
Points: 1,824, Visits: 3,497
Great spackle Jeff! Thanks for putting this brief article together - an easy and informative read.

cheers,




Steve.
Post #1028373
Posted Tuesday, November 30, 2010 9:51 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 10:39 AM
Points: 1,194, Visits: 787
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




Post #1028382
Posted Tuesday, November 30, 2010 10:06 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 8:18 PM
Points: 17,824, Visits: 15,756
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
Post #1028387
Posted Tuesday, November 30, 2010 10:11 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 17, 2014 10:23 AM
Points: 156, Visits: 599
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
Post #1028392
Posted Tuesday, November 30, 2010 10:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 35,372, Visits: 31,919
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1028395
Posted Tuesday, November 30, 2010 10:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 35,372, Visits: 31,919
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1028399
Posted Tuesday, November 30, 2010 10:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 35,372, Visits: 31,919
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1028400
Posted Tuesday, November 30, 2010 10:32 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 35,372, Visits: 31,919
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1028402
Posted Tuesday, November 30, 2010 10:38 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 10:39 AM
Points: 1,194, Visits: 787
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
Post #1028404
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse