Thread: where'd the spaces come from
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- 03SW/ 041- 05NE/ 011- 06NE/ 011- 01NE/ 011- 03NE/ 011- 02NE/ 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
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
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
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, Pennsylvania19026
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
> 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, Pennsylvania19026
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