Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Why doesn’t ISNUMERIC work correctly? (SQL Spackle) Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, November 30, 2010 9:00 PM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 7:19 PM Points: 42,047, Visits: 39,434
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1028368
 Posted Tuesday, November 30, 2010 9:27 PM
 SSCommitted Group: Moderators Last Login: Saturday, November 5, 2016 7:09 PM Points: 1,896, Visits: 3,753
 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 Group: General Forum Members Last Login: Thursday, December 31, 2015 5:26 AM Points: 1,212, 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 `
Post #1028382
 Posted Tuesday, November 30, 2010 10:06 PM
 SSC-Insane Group: General Forum Members Last Login: Monday, November 21, 2016 11:03 AM Points: 20,009, Visits: 18,255
 Good stuff Jeff. Got that spackle article out pretty quick. Jason AKA CirqueDeSQLeilI have given a name to my pain...MCM SQL Server, MVPSQL RNNRPosting Performance Based Questions - Gail Shaw
Post #1028387
 Posted Tuesday, November 30, 2010 10:11 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Friday, November 18, 2016 2:51 PM Points: 182, Visits: 774
 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-Forever Group: General Forum Members Last Login: Yesterday @ 7:19 PM Points: 42,047, Visits: 39,434
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1028395
 Posted Tuesday, November 30, 2010 10:26 PM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 7:19 PM Points: 42,047, Visits: 39,434
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1028399
 Posted Tuesday, November 30, 2010 10:28 PM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 7:19 PM Points: 42,047, Visits: 39,434
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1028400
 Posted Tuesday, November 30, 2010 10:32 PM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 7:19 PM Points: 42,047, Visits: 39,434
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1028402
 Posted Tuesday, November 30, 2010 10:38 PM
 Ten Centuries Group: General Forum Members Last Login: Thursday, December 31, 2015 5:26 AM Points: 1,212, 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 URLhttp://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html
Post #1028404

 Permissions