CODEWITHSUNDEEP
sqlremove-if
Vanessa Dickerman
Vanessa Dickerman

Reputation: 23

Remove observations with specific numbers

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?

Upvotes: 0

Views: 67

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270523

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

Upvotes: 1

Related Questions