Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 IsNumeric Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, March 20, 2008 5:53 AM
 SSC Veteran Group: General Forum Members Last Login: Monday, November 04, 2013 11:16 PM Points: 221, Visits: 109
 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
Post #472175
 Posted Thursday, March 20, 2008 6:12 AM
 SSC Veteran Group: General Forum Members Last Login: Monday, December 02, 2013 3:48 AM Points: 228, Visits: 626
 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 patindexJez
Post #472185
 Posted Thursday, March 20, 2008 8:20 AM
 SSChasing Mays Group: General Forum Members Last Login: Tuesday, August 20, 2013 2:16 PM Points: 655, Visits: 6,099
 The same holds true for monetary values , '\$1,234'.DAB
Post #472284
 Posted Thursday, March 20, 2008 12:58 PM
 Valued Member Group: General Forum Members Last Login: Tuesday, November 30, 2010 11:03 AM Points: 70, Visits: 31
 I think you can use something like this:`CREATE FUNCTION PureNumeric( @STRING VARCHAR(255))RETURNS BITASBEGINIF ISNUMERIC(@STRING) = 1BEGIN IF (CHARINDEX('D', @STRING) <> 0) OR (CHARINDEX('E', @STRING) <> 0) OR ( CHARINDEX('\$', @STRING) <> 0) RETURN 0 ELSE RETURN 1ENDRETURN 0END`I did this for you as an idea!Cheers,G
Post #472503
 Posted Friday, March 21, 2008 11:19 AM
 SSC-Addicted Group: General Forum Members Last Login: Monday, November 04, 2013 4:22 PM Points: 480, Visits: 467
 Another potential idea is something like:declare @test table(tString varchar(12))insert into @testselect null union allselect '' union allselect '\$1,234' union allselect '1111D1' union allselect '11E2' union allselect '-1.2' union all -- Valid numericselect '+0.5' union all -- Valid numericselect '+1.3.2' union allselect '215' union all -- Valid numericselect '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 isTestedfrom @test/* -------- Sample Output: --------tstring isNumeric isTested------------ ----------- -----------NULL 0 0 0 0\$1,234 1 01111D1 1 011E2 1 0-1.2 1 1+0.5 1 1+1.3.2 0 0215 1 1215+ 0 0*/
Post #473000
 Posted Wednesday, March 26, 2008 2:33 AM
 SSCrazy Group: General Forum Members Last Login: 2 days ago @ 6:43 AM Points: 2,378, Visits: 3,350
 If only digits are allowed, try thisSELECT *FROM Table1WHERE Col1 NOT LIKE '%[^0-9]%' N 56°04'39.16"E 12°55'05.25"
Post #474557
 Posted Wednesday, October 13, 2010 2:02 PM
 SSC Journeyman Group: General Forum Members Last Login: Thursday, November 14, 2013 11:03 AM Points: 80, Visits: 204
 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 numberswhere PatIndex('%[^0-9]%',(RTRIM(LTRIM(YourColName)))) = 0Thanks,Eddie H thanks, ERH
Post #1003960

 Permissions