March 20, 2008 at 5:53 am
Hi,
May be u all know this early but i wanted to post this.
I came across a phenomenon with the isnumeric function. Whenever we use
select isnumeric('1111d1')
the result gives as 1 instead of zero this happens for
select isnumeric('111d11')
select isnumeric('11d111')
and not for select isnumeric('11111d') or select isnumeric('1d1111') or select isnumeric('d11111').
The same happens when 'e' is used. for all other alphabets it works fine.
Thanks & Regards,
Balamurugan G
March 20, 2008 at 6:12 am
isNumeric returns True (1) when the string can be converted into a number.
'1111D1' can be converted into a number 1111 - D for decimal
'11E2' can also be converted into a number 1100 - E for exponent (power of 10).
If you need to check that the string only contains numerics then you will need a different piece of code e.g patindex
Jez
March 20, 2008 at 8:20 am
The same holds true for monetary values , '$1,234'.
DAB
March 20, 2008 at 12:58 pm
I think you can use something like this:
CREATE FUNCTION PureNumeric
(
@STRING VARCHAR(255)
)
RETURNS BIT
AS
BEGIN
IF ISNUMERIC(@STRING) = 1
BEGIN
IF (CHARINDEX('D', @STRING) <> 0) OR (CHARINDEX('E', @STRING) <> 0) OR ( CHARINDEX('$', @STRING) <> 0)
RETURN 0
ELSE
RETURN 1
END
RETURN 0
END
I did this for you as an idea!
Cheers,
G
March 21, 2008 at 11:19 am
Another potential idea is something like:
declare @test-2 table(tString varchar(12))
insert into @test-2
select null union all
select '' union all
select '$1,234' union all
select '1111D1' union all
select '11E2' union all
select '-1.2' union all -- Valid numeric
select '+0.5' union all -- Valid numeric
select '+1.3.2' union all
select '215' union all -- Valid numeric
select '215+'
-- --------------------------------------------------------------------------
-- (1) '[-.+0-9' provides for a leading sign or decimal place
-- (2) '[.0-9]' provides for digits or a decimal point in the string
-- (3) The length comparison makes sure that there is only 1 decimal point
-- --------------------------------------------------------------------------
select
tstring,
isNumeric(tString) as [isNumeric],
case when len(tString) - len(replace(tString,'.','')) <= 1
and tString like '[-.+0-9]' + replicate('[.0-9]', len(tstring) - 1)
then 1 else 0 end
as isTested
from @test-2
/* -------- Sample Output: --------
tstring isNumeric isTested
------------ ----------- -----------
NULL 0 0
0 0
$1,234 1 0
1111D1 1 0
11E2 1 0
-1.2 1 1
+0.5 1 1
+1.3.2 0 0
215 1 1
215+ 0 0
*/
March 26, 2008 at 2:33 am
If only digits are allowed, try this
SELECT*
FROMTable1
WHERECol1 NOT LIKE '%[^0-9]%'
N 56°04'39.16"
E 12°55'05.25"
October 13, 2010 at 2:02 pm
This is an example using the PatIndex. I believe it's just one of many options but will work. Just add this WHERE Clause to your statement to return only items that are truely simple numbers
where PatIndex('%[^0-9]%',(RTRIM(LTRIM(YourColName)))) = 0
Thanks,
Eddie H
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply