April 29, 2004 at 11:57 am
Hi all.
When I execute:
PRINT ISNUMERIC('+')or
PRINT ISNUMERIC('-')The result is 1!!! o_O
And, if I try to cast it to a numeric value the operation fails
declare @str_val nvarchar(10), @int_val int set @str_val = N'-' SET @int_val = CASE WHEN ISNUMERIC(@str_val) = 1 THEN CONVERT(int, CONVERT(float, @str_val)) ELSE 0 END print @int_val
You know why isn't this behavior uniform for all sentences?
How have you dealed with this?
I'm supposed to evaluate whether (IsNumeric), (NOT '+') and (NOT '-') for all validations?
Thanks
April 29, 2004 at 1:20 pm
I'm assuming this is some sort of parsing routine, so I won't ask why you care. My guess is that + and - are not just characters, but operators as well, so there may be some issue there. Most likely a bug. Other operators appear to return 0.
You can handle this by looking for those characters first and discarding them, then checking for more numerics.
April 29, 2004 at 4:28 pm
Yes, is part of a routine of the type "add to string field, and if is number add to the number field too".
Yes again, I thought it was a bug, but I don't wanted to assume things, and I decided to ask first.
Hmmm, I guess I'll do the evaluate whether (IsNumeric), (NOT '+') and (NOT '-') for all validations part >:-|
Thanks Steve!
April 30, 2004 at 3:33 am
Noticed that a single £ or $ evaluate to true aswell - guess they're classified as valid money types, and also a period, so there might be some others. Maybe an idea to have separate check for the single character case?
April 30, 2004 at 4:40 am
ISNUMERIC returns 1 for all valid 'numeric chars'
ie - not just 0-9, but operators and punctuations as well.
(I believe everything that money can handle)
Sometimes this is not wanted, we just want a 'numeric' to a digit 0-9 and nothing else. ISNUMERIC won't work then.
My preference is instead to cast the value to a string and use LIKE to determine if the value qualifies for my current wanting of what a 'numeric' should represent. (or any other pattern for that matter)
There are always several different ways to find what you're looking for - either equals something, or not equals something, so I've made a compilation of different ways to substitute ISNUMERIC with LIKE and wildcard ranges.
Enjoy.
=;o)
/Kenneth
-- start examples
-- Script downloaded 4/30/2004 5:33:39 AM
-- From The SQL Server Worldwide User's Group (www.sswug.org)
-- Scripts provided AS-IS without warranty of any kind use at your own risk
2003-03-10 / Kenneth Wilhelmsson
Some samples of how to use wildcard ranges
For the sake of argument,
a-z are 'alpha'
0-9 are 'numeric'
any other char is 'special'
drop table #x
create table #x ( field varchar(10) not null, type varchar(20) not null )
insert #x select 'abc', 'alpha only'
insert #x select 'abc1', 'alpha and numeric'
insert #x select '123' , 'numeric only'
insert #x select 'a2c', 'alpha and numeric'
insert #x select 'abc.-!', 'alpha + special'
insert #x select '.-%!', 'special'
Q: How do I find values that has NO digits (only alpha chars)?
A: returns rows where field has no digits (0-9) at all
select * from #x
where FIELD NOT LIKE '%[0-9]%'
field type
---------- --------------------
abc alpha only
abc.-! alpha + special
.-%! special
(3 row(s) affected)
Q: How do I find values that has at least ONE digit?
A: returns rows where field has at least one digit (0-9)
select * from #x
where FIELD LIKE '%[0-9]%'
field type
---------- --------------------
abc1 alpha and numeric
123 numeric only
a2c alpha and numeric
(3 row(s) affected)
Q: How do I find values that has ONLY standard (a-z) alpha chars and NO digits (0-9)?
A: returns rows where field has ONLY chars specified in the range
select * from #x
WHERE field NOT LIKE '%[^a-z]%'
field type
---------- --------------------
abc alpha only
(1 row(s) affected)
Q: How do I find values that has ONLY digits (0-9)?
A: returns rows where field has ONLY digits specified in the range
select * from #x
WHERE field NOT LIKE '%[a-z]%'
field type
---------- --------------------
123 numeric only
.-%! special
(2 row(s) affected)
oops - as can be seen this construct needs all special to be defined within the range as well..
select * from #x
WHERE field NOT LIKE '%[a-z.-%!]%'
field type
---------- --------------------
123 numeric only
(1 row(s) affected)
Q: How do I find values that has any chars EXCEPT ONLY digits (0-9)?
A: returns rows where field has any chars EXCEPT those who have ONLY digits
select * from #x
WHERE field LIKE '%[^0-9]%'
field type
---------- --------------------
abc alpha only
abc1 alpha and numeric
a2c alpha and numeric
abc.-! alpha + special
.-%! special
(5 row(s) affected)
or
WHERE field LIKE '%[a-z]%' -- this method also requires any special chars to be specified (eg more writing)..
field type
---------- --------------------
abc alpha only
abc1 alpha and numeric
a2c alpha and numeric
abc.-! alpha + special
(4 row(s) affected)
Q: How do I find values that has any combination of chars EXCEPT ONLY alphachars (a-z)?
A: returns rows where field has any combination of chars EXCEPT ONLY alphachars (a-z) as specified in the range
select * from #x
WHERE field LIKE '%[^a-z]%'
field type
---------- --------------------
abc1 alpha and numeric
123 numeric only
a2c alpha and numeric
abc.-! alpha + special
.-%! special
(5 row(s) affected)
Q: How do I find values that has any "special" charachters?
A: returns rows where any char NOT in the range exists
select * from #x
WHERE field LIKE '%[^a-z0-9]%'
field type
---------- --------------------
abc.-! alpha + special
.-%! special
(2 row(s) affected)
-- summary:
where FIELD NOT LIKE '%[0-9]%'
alpha only
alpha + special
special
where FIELD LIKE '%[0-9]%'
alpha and numeric
numeric only
WHERE field LIKE '%[^0-9]%'
WHERE field LIKE '%[a-z.!]%'
alpha only
alpha and numeric
alpha + special
special
WHERE field NOT LIKE '%[^a-z]%'
alpha only
WHERE field NOT LIKE '%[a-z.-%!]%'
WHERE field NOT LIKE '%[^0-9]%'
numeric only
WHERE field LIKE '%[^a-z0-9]%'
alpha + special
special
-- end examples
April 30, 2004 at 7:54 am
Searching a bit more I've found some more help,
Efectively, seems to be a bug, in the links are some scrips to build your own and reliable IsNumeric function (Similar to Kenneth's solution).
Other solution is to cast it to money!!, try: PRINT CAST('+' AS MONEY) and works...., no other data type is allowed.
Well, I expect this to be useful for someone else
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply