## IsNumeric

 Author Message balamurugan.ganesan SSC Veteran Group: General Forum Members Points: 263 Visits: 110 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 Jez-448386 SSCrazy Group: General Forum Members Points: 2514 Visits: 1158 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 Scalability Doug Hall of Fame Group: General Forum Members Points: 3277 Visits: 6111 The same holds true for monetary values , '\$1,234'.DAB Gonzalo-603688 SSC Veteran Group: General Forum Members Points: 234 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 Kent Waldrop SSCommitted Group: General Forum Members Points: 1926 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*/ SwePeso SSC-Insane Group: General Forum Members Points: 22405 Visits: 3433 If only digits are allowed, try thisSELECT *FROM Table1WHERE Col1 NOT LIKE '%[^0-9]%' N 56°04'39.16"E 12°55'05.25" @STLSQLGuy Ten Centuries Group: General Forum Members Points: 1090 Visits: 209 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