Remove observations with specific numbers

Question

I need to remove observations from the dataset that have a number after the decimal point. Some numbers got mixed into the date set that shouldn't be in and we have no what to tell which they are other than they have a number after the decimal point Ex: 9.42

Versus the real data is just 9.0, 10.0, 100.0.

Is there a way to do this in SQL?

What RDBMS are you using? SQL Server 2008? MySQL? Teradata? – JNevill Oct 22 '14 at 14:20 — JNevill, Oct 22 '14 at 14:20
What is the data type of the column? – Gordon Linoff Oct 22 '14 at 14:20 — Gordon Linoff, Oct 22 '14 at 14:20

score 1 · Answer 1 · answered Oct 22 '14 at 14:22

1

Something like this would work in most databases:

delete from table
    where number <> cast(number as int);

Or, if "number" is really a string:

delete from table
    where number like '%.%' and number not like '%.0';

answered Oct 22 '14 at 14:22

Gordon Linoff

1,242,037
58
646
786

Remove observations with specific numbers

1 Answers1