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

 Author Message Jeff Moden SSC Guru Group: General Forum Members Points: 432907 Visits: 43494 Comments posted to this topic are about the item Why doesn’t ISNUMERIC work correctly? (SQL Spackle) --Jeff ModenRBAR 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 problemsHow to post performance problemsForum FAQs stevefromOZ SSC-Insane Group: Moderators Points: 20006 Visits: 3757 Great spackle Jeff! Thanks for putting this brief article together - an easy and informative read.cheers, Steve. sharath.chalamgari SSCrazy Group: General Forum Members Points: 2948 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 SSC Guru Group: General Forum Members Points: 124696 Visits: 18627 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 autoexcrement SSCrazy Group: General Forum Members Points: 2189 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 SSC Guru Group: General Forum Members Points: 432907 Visits: 43494 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 ModenRBAR 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 problemsHow to post performance problemsForum FAQs Jeff Moden SSC Guru Group: General Forum Members Points: 432907 Visits: 43494 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 ModenRBAR 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 problemsHow to post performance problemsForum FAQs Jeff Moden SSC Guru Group: General Forum Members Points: 432907 Visits: 43494 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 ModenRBAR 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 problemsHow to post performance problemsForum FAQs Jeff Moden SSC Guru Group: General Forum Members Points: 432907 Visits: 43494 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 ModenRBAR 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 problemsHow to post performance problemsForum FAQs sharath.chalamgari SSCrazy Group: General Forum Members Points: 2948 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