It is quite often asked question how to select only numeric data from character based column. Using Sql Server makes this task usually easy, you just need to use IsNumeric function.

create table t1( 

	c1 varchar(10) 

);




insert into t1(c1) values('Krystian');

insert into t1(c1) values('10');

insert into t1(c1) values('13.5');

insert into t1(c1) values('- 13.5');

insert into t1(c1) values('100.25.55');



select * from t1;



Krystian

10

13.5

- 13.5

100.25.55



select * from t1

where isnumeric(c1) = 1



10

13.5

- 13.5

For this data it works fine, but does it handle all possibilities, the answer is no. IsNumeric function returns one for all the values, which can be converted to numeric. It also includes values with scientific notations. Lets try that:

insert into t1(c1) values('2e4');

insert into t1(c1) values('16d2');



select * from t1

where isnumeric(c1) = 1



10

13.5

- 13.5

2e4

16d2

A small surprise 2e4 and 16d4 were also selected, this is because those values are numbers written in scientific notation 2e4 represents 2 to power 4 and 16d2 represents square root of 16.
To resolve the problem we have to slightly modify our where clause.

select * from t1

where isnumeric(replace(replace(c1,'e','x'),'d','x')) = 1



10

13.5

- 13.5

We resolved the problem with scientific notations by replacing characters 'd' and 'e' with character 'x'.