如果数字是整数，如何删除to_char中的点 [英] How to remove the dot in to_char if the number is an integer

问题描述

我需要在postgresql中的数字字段上执行to_char。我还不能提出正确的格式字符串。我想寻求帮助。

I need to perform to_char on a numeric field in postgresql. I am not able to come up with the right format string yet. I would like to get some help.

test=# select to_char(1.2, 'FM9999.9999'), to_char(1, 'FM9999.9999'), to_char(1.2212, 'FM9999.9999');
 to_char | to_char | to_char
---------+---------+---------
 1.2     | 1.      | 1.2212
(1 row)

基本上，如果有小数点后无位。 to_char（1，fstring）应该导致 1 ，但是 to_char（1.23，fstring） 应为'1.23'。我需要 to_char 的行为与python中的 str（）函数完全一样。例如在python控制台中：

Basically, I should not have the dot if there is are no places after the decimal places. to_char(1, fstring) should result in 1, but to_char(1.23, fstring) should result in '1.23'. I need the to_char to behave exactly like str() function in python. For instance in python console:

>>> str(1.2)
'1.2'
>>> str(1)
'1'
>>> str(1.23)
'1.23'

我能找到的最接近的解决方案是使用强制转换功能。

closest solution I can find is to use the cast function.

=# select cast(1 as text), cast(1.2 as text), cast(12.3 as text), cast(1.0 as text);
 text | text | text | text
------+------+------+------
 1    | 1.2  | 12.3 | 1.0

有没有一种使用格式字符串实现相同的方法。如果是这样，有人可以为我提供正确的格式字符串吗？

Is there a way to use format string achieve the same. If so, can someone help me with the right format string?

推荐答案

您可以创建一个函数：

create function to_ch (value numeric, format text)
returns text language sql as $$
    select rtrim(to_char(value, format), '.')
$$;

select to_ch(1.2, 'FM9999.9999'), to_ch(1, 'FM9999.9999'), to_ch(1.2212, 'FM9999.9999');

 to_ch | to_ch | to_ch  
-------+-------+--------
 1.2   | 1     | 1.2212
(1 row)

预定义格式的变体（也许更方便）：

Variant with predefined format (maybe more handy):

create function to_ch4 (value numeric)
returns text language sql as $$
    select rtrim(to_char(value, 'FM9999.9999'), '.')
$$;

select to_ch4(1.2), to_ch4(1), to_ch4(1.2212);

 to_ch4 | to_ch4 | to_ch4 
--------+--------+--------
 1.2    | 1      | 1.2212
(1 row)

这篇关于如果数字是整数，如何删除to_char中的点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！