pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
where'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>
  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 10:10  Gary Stainburn <gary.stainburn@ringways.co.uk>
  parent: 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 11:00  Richard Huxton <dev@archonet.com>
  parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
  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-08-02 15:50  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: Richard Huxton <dev@archonet.com>
  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-08-02 15:56  Gary Stainburn <gary.stainburn@ringways.co.uk>
  parent: 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-08-02 16:31  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
  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-08-03 09:39  Richard Huxton <dev@archonet.com>
  parent: Bruce Momjian <pgman@candle.pha.pa.us>
  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