Reputation: 321

Select only numeric value

I have a table structure like below.

id            version     REQ_REF_ID
 3              1.2            6
 2              1.1            6
 1               1             6

My query is below

Select * from XYZ where REQ_REF_ID = 6606 order by version desc FETCH FIRST 2 ROWS ONLY

It gives me the latest 2 rows which id is 3 and 2.

But I want to get only those two row where version number is integer, not having decimal values.

In this case I want to get the row which id is 1.

Upvotes: 3

Answers (3)

Reputation: 186

you can check like

and (version)%1 = 0

% by 1 will give you decimal parts if any

Upvotes: 0

Reputation: 31648

You may also use TRANSLATE

 where translate(VERSION, '?1234567890', '?') is null

Upvotes: 0

Reputation: 58774

You can check against ROUND function to get integer values

and ROUND(version) = version

ROUND returns n rounded to integer places to the right of the decimal point.

Upvotes: 3