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 11:14 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 5:51 PM
Points: 17,845, Visits: 15,797
Jeff Moden (11/30/2010)
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.


I must have missed it. What is the link?




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1028418
Posted Wednesday, December 1, 2010 2:27 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld 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.
Post #1028475
Posted Wednesday, December 1, 2010 3:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 8:46 AM
Points: 4, Visits: 27
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
Post #1028496
Posted Wednesday, December 1, 2010 4:09 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 27, 2014 10:30 AM
Points: 59, Visits: 216
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
Post #1028505
Posted Wednesday, December 1, 2010 4:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 7:28 AM
Points: 5,584, Visits: 6,380
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

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.
Post #1028521
Posted Wednesday, December 1, 2010 5:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 27, 2014 7:46 AM
Points: 29, Visits: 235
Good catch... I've never thought about it.
Great article. Short and perfectly clear.
Thank you!
Post #1028528
Posted Wednesday, December 1, 2010 5:34 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld 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
Post #1028545
Posted Wednesday, December 1, 2010 5:38 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, December 16, 2013 7:19 AM
Points: 98, Visits: 226
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.
Post #1028548
Posted Wednesday, December 1, 2010 5:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 12:33 AM
Points: 1, Visits: 14
This function is OK for integer numbers, what about decimal or negative ?
Post #1028553
Posted Wednesday, December 1, 2010 5:53 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, September 22, 2014 10:49 AM
Points: 782, Visits: 731
please also consider 0E0 and numbers like them.
Post #1028555
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse