从 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;

它为我提供了 text 类型的工作日信息.然后我使用:

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'

把它从text改为character变化.

好像长度是固定的，不是按照每个字长来的.
例如，星期五"的长度应为 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?

因为稍后我会将这张表的 weekday 列与另一个表的 weekday 列进行比较.喜欢

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，不要转换为 text 并返回.
并且不要使用基本类型名称 date 作为列名称.改用 day.
我只会使用 text 作为文本.转换为 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屋！