I have a list of prices with decimals.
Example: 0.513 0.35135 1.6583
I'm trying to select the length of the decimal digits. Here's what I mean: 0.513 | length=3 0.35135 | length=5 1.6583 | length=4
I tried converting the numbers to text, but it doesn't seem to provide correct results:
select price, length(cast(price as text)) from table.prices
I also tried this conversion method: "length(price::text)"
Any advice on how I can achieve this?
You can use the scale function, since postgresql 9.6 ;)
select scale(1.12345) as decimal_places;
decimal_places
----------------
5
(1 row)
SQL Error [42883]: ERROR: function scale(double precision) does not exist. This would fix - select (scale(cast(your_col as numeric))) as decimal_places FROM your_table
This code works (see SQLFiddle Demo):
WITH test(x) AS (VALUES (0.513), (0.35135), (1.6583))
SELECT length((x - floor(x))::text)-2
FROM test
There is only one gotcha - for whole integers it returns -1.
SELECT length((x - floor(x::real))::text)-2How about this:
CREATE FUNCTION decimal_places(n numeric) RETURNS int
LANGUAGE SQL IMMUTABLE
AS $$
select coalesce(length(substring(cast($1 as text) from '\..*$')) - 1, 0);
$$;
=> select decimal_places(0.513);
┌────────────────┐
│ decimal_places │
├────────────────┤
│ 3 │
└────────────────┘
select double_value, length(split_part(double_value::text, '.', 2))
from your_table
To find how many decimal points, first convert double to text double_value::text, split text into two parts - the part before the decimal point and the part after the decimal point.
Take the part after the decimal point split_part(double_value::text, '.', 2). Finally, get the length of the part after the decimal point.
Write an sql or plpgsql function for this. (Or better yet, do this in your app: it probably doesn't belong in Postgres to begin with.)
The pseudocode you need is:
If you're dealing with stuff that have exponents, make sure you cast the values to numeric first, so as to expand e.g. 1e-12 to 0.000000000001.
In select can be used like this:
SELECT length((SELECT regexp_matches(price::text,'(\\.\\d+)')::text)), price FROM table.prices
