|
|
|
SSCoach
         
Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 18,754,
Visits: 12,337
|
|
|
|
|
|
Old Hand
      
Group: General Forum Members
Last Login: Friday, July 29, 2011 4:14 AM
Points: 318,
Visits: 198
|
|
| Thanks Jeff for this nice and very well explained article.
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Wednesday, April 17, 2013 11:10 PM
Points: 4,
Visits: 26
|
|
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
|
|
|
|
|
Valued Member
      
Group: General Forum Members
Last Login: Monday, May 20, 2013 11:19 AM
Points: 58,
Visits: 184
|
|
Very nice article. Well written and now added to my bookmarks.
Thanks for sharing
David Bridge
www.DavidBridgeTechnology.com
David Bridge David Bridge Technology Limited www.davidbridgetechnology.com
|
|
|
|
|
SSCertifiable
       
Group: General Forum Members
Last Login: Yesterday @ 12:39 PM
Points: 6,668,
Visits: 5,695
|
|
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 Administrator, MCDBA, MCSA
Webpage: http://www.BrandieTarvin.net LiveJournal Blog: http://brandietarvin.livejournal.com/ On LinkedIn!, Google+, and Twitter.
Freelance Writer: Shadowrun Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Wednesday, January 30, 2013 10:57 AM
Points: 28,
Visits: 173
|
|
Good catch... I've never thought about it. Great article. Short and perfectly clear. Thank you!
|
|
|
|
|
Old Hand
      
Group: General Forum Members
Last Login: Wednesday, February 20, 2013 9:40 AM
Points: 358,
Visits: 2,775
|
|
Hi Jeff,
Its very nice . Thanks a lot.
Thanks & Regards, MC
|
|
|
|
|
SSC Journeyman
      
Group: General Forum Members
Last Login: Thursday, April 18, 2013 6:58 AM
Points: 96,
Visits: 210
|
|
First off thanks, like others I had not stopped to think about "d" and "e" and I am a little surprised "x" was not included if they were. go figure.
However, when I ran your script on my machine the returned data set included 92 \ (backslash). Is there some setting that affects that?
I expanded your script a little:
--===== Return all characters that ISNUMERIC thinks is numeric -- (uses values 0-255 from the undocumented spt_Values table -- instead of a loop from 0-255) SELECT [Ascii Code] = STR(Number), [Ascii Character] = CHAR(Number), [Standalone] = ISNUMERIC(CHAR(Number)), [CharZero] = ISNUMERIC(CHAR(Number)+'0'), [ZeroCharZero] = ISNUMERIC('0'+CHAR(Number)+'0'), [ZeroCharCharZero] = ISNUMERIC('0'+CHAR(Number)+CHAR(Number)+'0'), [ZeroChar] = ISNUMERIC('0'+CHAR(Number)) FROM Master.dbo.spt_Values WHERE Type = 'P' AND Number BETWEEN 0 AND 255 AND (ISNUMERIC(CHAR(Number)) = 1 or 1 = ISNUMERIC(CHAR(Number)+'0') or 1 = ISNUMERIC('0'+CHAR(Number)+'0') or 1 = ISNUMERIC('0'+CHAR(Number)) )
And got this result set:
Ascii Code Character Standalone CharZero ZeroCharZero ZeroCharCharZero ZeroChar ---------- --------- ----------- ----------- ------------ ---------------- ----------- 0 0 0 1 1 1 9 1 1 0 0 0 10 1 1 0 0 0 11 1 0 0 0 1 12 1 0 0 0 1 13 1 1 0 0 0 32 0 1 0 0 1 36 $ 1 1 0 0 0 43 + 1 1 0 0 0 44 , 1 1 1 1 1 45 - 1 1 0 0 0 46 . 1 1 1 0 1 48 0 1 1 1 1 1 49 1 1 1 1 1 1 50 2 1 1 1 1 1 51 3 1 1 1 1 1 52 4 1 1 1 1 1 53 5 1 1 1 1 1 54 6 1 1 1 1 1 55 7 1 1 1 1 1 56 8 1 1 1 1 1 57 9 1 1 1 1 1 68 D 0 0 1 0 0 69 E 0 0 1 0 0 92 \ 1 1 0 0 0 100 d 0 0 1 0 0 101 e 0 0 1 0 0 128 € 1 1 0 0 0 160 1 0 0 0 1 162 ¢ 1 1 0 0 0 163 £ 1 1 0 0 0 164 ¤ 1 1 0 0 0 165 ¥ 1 1 0 0 0
Notice that 44 (comma) has a one in the ZeroCharCharZero column. Also that 0 has ones in the last three columns. I tried and select convert(numeric,'0,,0') go select convert(numeric,'0'+char(0)+'0') go select convert(numeric,'0'+char(0)+char(0)+'0') go select convert(numeric,'0'+char(0)) go
all throw a errors
Bottom line is from now on if I need to validate input I think I will put in a try block assigning the string to a variable of the actual type I need and if I need to validate columns use a type specific function.
Again, thanks for the food for thougt.
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Friday, December 17, 2010 1:22 AM
Points: 1,
Visits: 13
|
|
| This function is OK for integer numbers, what about decimal or negative ?
|
|
|
|
|
Right there with Babe
      
Group: General Forum Members
Last Login: 2 days ago @ 9:24 AM
Points: 770,
Visits: 682
|
|
| please also consider 0E0 and numbers like them.
|
|
|
|