从to_char（）输出中删除空白填充 [英] Remove blank-padding from to_char() output

问题描述

我从此生成一个视图：

create or replace view datetoday as
select to_char(dt, 'yyyy-mm-dd') as date, to_char(dt, 'Day') as weekday from 
(select ('2013-03-01'::date + i) dt from generate_series(0,'2013-03-03'::date - 2013-03-01'::date) 
as t(i)) as t;

它给我的工作日信息为文本类型。然后我使用：

It gives me the weekday info as text type. Then I use:

select date::date, weekday::varchar from datetoday;

现在的表格就像

2013-3-1 Friday
2013-3-2 Saturday

如果我想选择条目：

select * from datetoday where weekday='Friday'

将其从文本更改为字符不同的。

似乎长度是固定的不是根据每个字长度。

例如'Friday '应该有6和星期三长度9.

我该如何更改这个，把长度作为单词的实际长度？

It seems that the length is fixed is not according to each word length.
For example 'Friday' should have length 6 and Wednesday length 9.
How can I change this, let the length be the actual length of the word?

因为稍后我会将此表的工作日列与其他表的工作日列进行比较。像

Because later I will compare this table's weekday column to another table's weekday column. Like

where a.weekday=b.weekday

其他工作日来自jsp的用户，所以长度有所不同。

现在长度是固定的，比较失败。

The other weekday is from user from jsp, so the length varies.
Now the length is fixed, the comparison fails.

推荐答案

模式'Day'在空白填充到右边，使所有日子长9个字符。使用 FM 模板模式修饰符以删除任何填充：

The pattern 'Day' is blank-padded to the right, making all days 9 characters long. Use the FM Template Pattern Modifier to remove any padding:

SELECT d::date AS day
     , to_char(d, 'yyyy-mm-dd') AS day_text
     , to_char(d, 'FMDay')      AS weekday
FROM generate_series('2013-03-01'::date
                   , '2013-03-07'::date
                   , interval '1 day') d;

还演示 generate_series（）用于时间戳。一个较少的查询级别。

如果您在视图中需要实际的日期，请将其设为实际类型 date ，不要转换为文本并返回。

不要使用基本类型名称 date 作为列名。使用天。

我只需使用文本作为文本。没有点转换为 varchar 。

Also demonstrating generate_series() for timestamps. One less query level.
If you need an actual date in the view, make it an actual type date, don't convert to text and back.
And don't use the basic type name date as column name. Using day instead.
And I would just use text for the text. No point in converting to varchar.

这篇关于从to_char（）输出中删除空白填充的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！