Join Us!

Spaces are allowed; punctuation is not allowed except for periods, hyphens, and underscores.
A valid e-mail address. All e-mails from the system will be sent to this address. The e-mail address is not made public and will only be used if you wish to receive a new password or wish to receive certain news or notifications by e-mail.
CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.

Login

Enter your Project Envision username.
Enter the password that accompanies your username.
Request new password

Filtering numeric data from character based column

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'.