How to check if the value is numeric ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

How to check if the value is numeric ?

Author

Topic

konark
Yak Posting Veteran

60 Posts

Posted - 2009-10-29 : 20:14:52

if the value is 10&2 It should return null
if the value is -10.3 , returns -10.3

Chandragupta Mourya

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-10-29 : 21:24:53

SELECT CASE IsNumeric(myColumn) WHEN 1 THEN myColumn ELSE Null END FROM myTable

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-10-29 : 22:05:03

careful with that.

Consider this:

select isnumeric(10&2) -- true
select isnumeric('10&2') -- false
select isnumeric('10.2') -- true
select isnumeric(10^2) -- true
select isnumeric('10^2') -- false

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-30 : 03:04:53

One of the methods is



select data from
(
	select '10$2' as  data union all
	select '10&2' union all
	select '--10.3' union all
	select '-10.3' union all
	select '-10.3.6' union all
	select '-10,3' 
) as t

where 
	data not like '%[^-0-9.]%' and 
	len(data)-len(replace(data,'.',''))<2 and 
	len(data)-len(replace(data,'-',''))<2

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources