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

IsNumeric Expand / Collapse
Author
Message
Posted Thursday, March 20, 2008 5:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 17, 2014 4:33 AM
Points: 221, 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
Post #472175
Posted Thursday, March 20, 2008 6:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 7:39 AM
Points: 231, Visits: 655
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
Post #472185
Posted Thursday, March 20, 2008 8:20 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 2:01 PM
Points: 659, Visits: 6,104
The same holds true for monetary values , '$1,234'.


DAB
Post #472284
Posted Thursday, March 20, 2008 12:58 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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 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
Post #472503
Posted Friday, March 21, 2008 11:19 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 4, 2013 4:22 PM
Points: 480, Visits: 467
Another potential idea is something like:

declare @test table(tString varchar(12))
insert into @test
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

/* -------- 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
*/
Post #473000
Posted Wednesday, March 26, 2008 2:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 4:11 AM
Points: 2,397, Visits: 3,416
If only digits are allowed, try this

SELECT *
FROM Table1
WHERE 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

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 10, 2014 7:25 AM
Points: 80, Visits: 205
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



thanks, ERH
Post #1003960
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse