I have table called nums.
CREATE TABLE nums (num numeric);
INSERT INTO nums SELECT * FROM (VALUES(10.11),(11.122),(12.22),(13.555)) t;
It have following rows
select * from nums
num
--------
10.11
11.122
12.22
13.555
So my question is how to get the values with only two digits after decimal point(.)?
Expected Output :-
num
--------
10.11
12.22
You can try Pattern Matching
SELECT * FROM nums WHERE num::text ~ '(^\d+(\.\d{1,2})?$)';
or Mathematical Functions and Operators - Floor
SELECT * FROM nums WHERE FLOOR (num*100)=num*100
0 or 111.1. sqlfiddle.com/#!15/7f89c/3Simply with left() and right():
SELECT * FROM nums WHERE left(right(num::text, 3), 1) = '.';
Or with split_part():
SELECT * FROM nums WHERE length(split_part(num::text, '.', 2)) = 2;
Or take the number modulo 1 and cast to text:
SELECT * FROM nums WHERE length((num%1)::text) = 4
The last one is independent of the character used for comma.
Aside: you can simplify your INSERT statement:
INSERT INTO nums VALUES(10.11),(11.122),(12.22),(13.555);
I ran a quick test with a temp table of 100k numbers:
SELECT * FROM nums WHERE ...
left(right(num::text, 3), 1) = '.'; -- Total runtime: 136 ms
length(split_part(num::text, '.', 2)) = 2; -- Total runtime: 162 ms
length((num%1)::text) = 4; -- Total runtime: 179 ms
num::text ~ '(^\d+(\.\d{1,2})?$)'; -- Total runtime: 280 ms (incorrect!)
FLOOR (num*100)=num*100; -- Total runtime: 203 ms (incorrect!)
The last two (provided by @varchar) are actually incorrect. See comments and fiddle.
[0-9]+\.[0-9][0-9]should do it