2

How to show only real number with SQL Select ?

As example the data in table appear like :

Price

100000

10.0022

99980

0.0005

1.5

25000

0

20.5

The price data type is number.

I try SQL query like this but fail :

SELECT price FROM price_table WHERE price LIKE '%.%';

The query return error :

ERROR:  operator does not exist: numeric ~~ unknown
Mr. Mike
  • 453
  • 5
  • 23
  • 1
    Possible duplicate of [Fetch records that are non zero after the decimal point in PostgreSQL](http://stackoverflow.com/questions/15240652/fetch-records-that-are-non-zero-after-the-decimal-point-in-postgresql) – artm Dec 02 '16 at 06:35

1 Answers1

6

LIKE is for strings, not for numbers. If you want to find those that do have decimal digits, compare them to their integer value:

select *
from price_table
where price::int <> price;

To be able to use LIKE you need to first convert the number to a string:

select *
from price_table
where price::text like '%.%';