using IsNumeric - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

using IsNumeric

Author

Topic

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2002-07-30 : 06:02:09

I've got a table column (varchar) that contains numbers as well as words. A sample is the second column:

local 121
local OVERRIDE
local2 100
local 131

I'm trying to write a statement that groups by the first column and counts only the numbers in the second. In the above example, my result would be:

local 2
local2 1

I'm looking at ISNUMERIC as a possibility but can't seem to figure out how to apply it. Is this the best or is there something better I'm missing?

macka
Posting Yak Master

162 Posts

Posted - 2002-07-30 : 06:15:45

This seems to work. It uses a derived table:

select x.col1,sum(num)
from (
select col1,isnumeric(col2) as num
from t
) x
group by x.col1

macka.

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2002-07-30 : 06:43:44

Thanks - I can make that work.

rksingh024
Yak Posting Veteran

56 Posts

Posted - 2002-07-30 : 06:51:38

Second way is:

select col1,
sum(ISNUMERIC(col2)) RecCount
from tableName
group by col1

Ramesh

Edited by - rksingh024 on 07/30/2002 06:52:24

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-07-30 : 07:11:00

Remember however that ISNUMERIC can't really be relied on and you should code defensively. i.e. ISNUMERIC('2e2') = 1

 
create table test(col1 varchar(10),col2 varchar(50))
go

insert test select  'local','121'
      union select  'local','OVERRIDE'
      union select  'local2','100.23' 
      union select  'local','131'
      union select  'local2','$'
      union select  'local2','2e2'
go

select col1, SUM(ISNUMERIC(col2)) as 'IsNumericCount'
from test
group by col1
order by col1
go

select col1,sum(CASE WHEN (ISNUMERIC(col2)=1 AND PATINDEX('%[^0-9,.]%',col2)=0)
                     THEN 1 ELSE 0 END) as 'Numeric Count'
from test
group by col1
order by col1
go

drop table test
go

Results :

col1       IsNumericCount 
---------- -------------- 
local      2
local2     3

col1       Numeric Count 
---------- ------------- 
local      2
local2     1

HTH
Jasper Smith

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-07-30 : 07:23:37

ISNUMERIC(NCHAR(0x221E)) = 1 because CAST(NCHAR(0x221E) AS float) = 8.0.
NCHAR(0x221E) is the infinity character. Brings a whole new meaning to "fall over"

Edited by - Arnold Fribble on 07/30/2002 07:25:00

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources