pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedwhere'd the spaces come from
7+ messages / 3 participants
[nested] [flat]
* where'd the spaces come from
@ 2001-07-23 09:18 Gary Stainburn <gary.stainburn@ringways.co.uk>
2001-07-23 10:10 ` Re: where'd the spaces come from Gary Stainburn <gary.stainburn@ringways.co.uk>
2001-07-23 11:00 ` Re: where'd the spaces come from Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 7+ messages in thread
From: Gary Stainburn @ 2001-07-23 09:18 UTC (permalink / raw)
To: pgsql-sql
Hi all,
Can someone please explain how to remove the spaces from the results of the
query below. The current output is also included. What I want out of the
query is something like 'NE/027-05'.
psql -c "select t.tregion || '/' || to_char(t.tnumber,'000') || '-' ||
to_char(m.mnumber,'00') as unitno from teams t, members m
where m.mteam = t.tid;"
unitno
-------------
SW/ 041- 03
SW/ 041- 05
NE/ 011- 06
NE/ 011- 01
NE/ 011- 03
NE/ 011- 02
NE/ 011- 10
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: where'd the spaces come from
2001-07-23 09:18 where'd the spaces come from Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2001-07-23 10:10 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
1 sibling, 0 replies; 7+ messages in thread
From: Gary Stainburn @ 2001-07-23 10:10 UTC (permalink / raw)
To: pgsql-sql
Hi all,
forget it, I've solved it. I converted the calls to:
to_char(t.tnumber,'FM000')
and it worked.
Gary
On Monday 23 July 2001 10:18 am, Gary Stainburn wrote:
> Hi all,
>
> Can someone please explain how to remove the spaces from the results of the
> query below. The current output is also included. What I want out of the
> query is something like 'NE/027-05'.
>
> psql -c "select t.tregion || '/' || to_char(t.tnumber,'000') || '-' ||
> to_char(m.mnumber,'00') as unitno from teams t, members m
> where m.mteam = t.tid;"
> unitno
> -------------
> SW/ 041- 03
> SW/ 041- 05
> NE/ 011- 06
> NE/ 011- 01
> NE/ 011- 03
> NE/ 011- 02
> NE/ 011- 10
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: where'd the spaces come from
2001-07-23 09:18 where'd the spaces come from Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2001-07-23 11:00 ` Richard Huxton <dev@archonet.com>
2001-08-02 15:50 ` Re: where'd the spaces come from Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 7+ messages in thread
From: Richard Huxton @ 2001-07-23 11:00 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql
From: "Gary Stainburn" <gary.stainburn@ringways.co.uk>
> psql -c "select t.tregion || '/' || to_char(t.tnumber,'000') || '-' ||
> to_char(m.mnumber,'00') as unitno from teams t, members m
> where m.mteam = t.tid;"
> unitno
> -------------
> SW/ 041- 03
> SW/ 041- 05
Looks like a buglet in to_char()s handling of numbers (unless I
misunderstand the way the formatting is supposed to work).
select '[' || to_char(12,'x000') || ']';
?column?
----------
[x 012]
If you're running the current version, might be worth posting a bug report.
You can work around it with something like:
... substr(to_char(t.tnumber,'000'),2,3) ...
HTH
- Richard Huxton
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: where'd the spaces come from
2001-07-23 09:18 where'd the spaces come from Gary Stainburn <gary.stainburn@ringways.co.uk>
2001-07-23 11:00 ` Re: where'd the spaces come from Richard Huxton <dev@archonet.com>
@ 2001-08-02 15:50 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-08-02 15:56 ` Re: where'd the spaces come from Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 7+ messages in thread
From: Bruce Momjian @ 2001-08-02 15:50 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql
Does anyone have a fix for this?
> From: "Gary Stainburn" <gary.stainburn@ringways.co.uk>
>
> > psql -c "select t.tregion || '/' || to_char(t.tnumber,'000') || '-' ||
> > to_char(m.mnumber,'00') as unitno from teams t, members m
> > where m.mteam = t.tid;"
> > unitno
> > -------------
> > SW/ 041- 03
> > SW/ 041- 05
>
> Looks like a buglet in to_char()s handling of numbers (unless I
> misunderstand the way the formatting is supposed to work).
>
> select '[' || to_char(12,'x000') || ']';
> ?column?
> ----------
> [x 012]
>
> If you're running the current version, might be worth posting a bug report.
> You can work around it with something like:
>
> ... substr(to_char(t.tnumber,'000'),2,3) ...
>
> HTH
>
> - Richard Huxton
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: where'd the spaces come from
2001-07-23 09:18 where'd the spaces come from Gary Stainburn <gary.stainburn@ringways.co.uk>
2001-07-23 11:00 ` Re: where'd the spaces come from Richard Huxton <dev@archonet.com>
2001-08-02 15:50 ` Re: where'd the spaces come from Bruce Momjian <pgman@candle.pha.pa.us>
@ 2001-08-02 15:56 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2001-08-02 16:31 ` Re: where'd the spaces come from Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Gary Stainburn @ 2001-08-02 15:56 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
Hi Bruce,
a fix for what?
If you're meaning the leading space, then the fix is in the followup post
that I made to my original quiestion. i.e.
psql -c "select to_char(12,'xFM000');"
to_char
---------
x012
(1 row)
The 'FM' removes the space.
Gary
On Thursday 02 August 2001 4:50 pm, Bruce Momjian wrote:
> Does anyone have a fix for this?
>
> > From: "Gary Stainburn" <gary.stainburn@ringways.co.uk>
> >
> > > psql -c "select t.tregion || '/' || to_char(t.tnumber,'000') || '-' ||
> > > to_char(m.mnumber,'00') as unitno from teams t, members m
> > > where m.mteam = t.tid;"
> > > unitno
> > > -------------
> > > SW/ 041- 03
> > > SW/ 041- 05
> >
> > Looks like a buglet in to_char()s handling of numbers (unless I
> > misunderstand the way the formatting is supposed to work).
> >
> > select '[' || to_char(12,'x000') || ']';
> > ?column?
> > ----------
> > [x 012]
> >
> > If you're running the current version, might be worth posting a bug
> > report. You can work around it with something like:
> >
> > ... substr(to_char(t.tnumber,'000'),2,3) ...
> >
> > HTH
> >
> > - Richard Huxton
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: where'd the spaces come from
2001-07-23 09:18 where'd the spaces come from Gary Stainburn <gary.stainburn@ringways.co.uk>
2001-07-23 11:00 ` Re: where'd the spaces come from Richard Huxton <dev@archonet.com>
2001-08-02 15:50 ` Re: where'd the spaces come from Bruce Momjian <pgman@candle.pha.pa.us>
2001-08-02 15:56 ` Re: where'd the spaces come from Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2001-08-02 16:31 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-08-03 09:39 ` Re: where'd the spaces come from Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 7+ messages in thread
From: Bruce Momjian @ 2001-08-02 16:31 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-sql
> Hi Bruce,
>
> a fix for what?
> If you're meaning the leading space, then the fix is in the followup post
> that I made to my original quiestion. i.e.
>
> psql -c "select to_char(12,'xFM000');"
> to_char
> ---------
> x012
> (1 row)
>
> The 'FM' removes the space.
So the FM is the correct way to do this, right? There is no bug?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: where'd the spaces come from
2001-07-23 09:18 where'd the spaces come from Gary Stainburn <gary.stainburn@ringways.co.uk>
2001-07-23 11:00 ` Re: where'd the spaces come from Richard Huxton <dev@archonet.com>
2001-08-02 15:50 ` Re: where'd the spaces come from Bruce Momjian <pgman@candle.pha.pa.us>
2001-08-02 15:56 ` Re: where'd the spaces come from Gary Stainburn <gary.stainburn@ringways.co.uk>
2001-08-02 16:31 ` Re: where'd the spaces come from Bruce Momjian <pgman@candle.pha.pa.us>
@ 2001-08-03 09:39 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 7+ messages in thread
From: Richard Huxton @ 2001-08-03 09:39 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
From: "Bruce Momjian" <pgman@candle.pha.pa.us>
> > Hi Bruce,
> >
> > a fix for what?
> > If you're meaning the leading space, then the fix is in the followup
post
> > that I made to my original quiestion. i.e.
> >
> > psql -c "select to_char(12,'xFM000');"
> > to_char
> > ---------
> > x012
> > (1 row)
> >
> > The 'FM' removes the space.
>
> So the FM is the correct way to do this, right? There is no bug?
Well - it's certainly *unexpected* behaviour isn't it?
It is documented though (Karel Zak's given plenty of examples too):
"FM suppresses leading zeroes or trailing blanks that would otherwise be
added to make the output of a pattern be fixed-width"
Some of the examples show the difference too:
to_char(12,'9990999.9') => ' 0012.0'
to_char(12,'FM9990999.9') => '0012'
I think the issue is you look at to_char() and make assumptions if you're
not familiar with it. I *seem* to remember someone saying Oracle worked this
way.
- Richard Huxton
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2001-08-03 09:39 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-07-23 09:18 where'd the spaces come from Gary Stainburn <gary.stainburn@ringways.co.uk>
2001-07-23 10:10 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2001-07-23 11:00 ` Richard Huxton <dev@archonet.com>
2001-08-02 15:50 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-08-02 15:56 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2001-08-02 16:31 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-08-03 09:39 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox