Thread: to_char() Question
Greetings List:
I am using to_char to format numeric data into a string that is ultimately
displayed in an XmText widget. Much of the numeric data is always going to be
positive. In some of the windows that display this data, space is at a
premium. Basically, I need to display something like 1,029 in an XmText
widget that is exactly 5 characters wide. In a select statement like:
SELECT to_char (1029, '9,999'), to_char always puts one extra space in the
leftmost position for a numeric sign. What I get is: ' 1,029', which causes
me to have to have the XmText widget 6 characters wide as opposed to 5. This
is becoming a problem on several of the application windows where, as I said
earlier, space is at a premium. The only way that I've been able to suppress
the extra space is by the following: SELECT to_char (1029, 'FM9,999'). What I
get then is a string that is not right justified and I want the numbers to be
right justified. Now, finally, the question: Is there a way to suppress the
extra space for the sign?
TIA
rnd=# select version();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-49)
(1 row)
Not sure if there is a numeric formatting option that allows what you want. But how about? substr(to_char(1029, '9,999'),2) John Terry Lee Tucker said: > Greetings List: > > I am using to_char to format numeric data into a string that is ultimately > displayed in an XmText widget. Much of the numeric data is always going to > be > positive. In some of the windows that display this data, space is at a > premium. Basically, I need to display something like 1,029 in an XmText > widget that is exactly 5 characters wide. In a select statement like: > SELECT to_char (1029, '9,999'), to_char always puts one extra space in the > leftmost position for a numeric sign. What I get is: ' 1,029', which > causes > me to have to have the XmText widget 6 characters wide as opposed to 5. > This > is becoming a problem on several of the application windows where, as I > said > earlier, space is at a premium. The only way that I've been able to > suppress > the extra space is by the following: SELECT to_char (1029, 'FM9,999'). > What I > get then is a string that is not right justified and I want the numbers to > be > right justified. Now, finally, the question: Is there a way to suppress > the > extra space for the sign? > > TIA > rnd=# select version(); > version > -------------------------------------------------------------------------------------------------------------- > PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) > 3.2.3 > 20030502 (Red Hat Linux 3.2.3-49) > (1 row) > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >
On Tuesday 13 December 2005 11:20 am, John Sidney-Woollett saith: > Not sure if there is a numeric formatting option that allows what you want. > > But how about? > > substr(to_char(1029, '9,999'),2) That's so simple, I'm embarrased ;o) Thanks for the help... > > John > > Terry Lee Tucker said: > > Greetings List: > > > > I am using to_char to format numeric data into a string that is > > ultimately displayed in an XmText widget. Much of the numeric data is > > always going to be > > positive. In some of the windows that display this data, space is at a > > premium. Basically, I need to display something like 1,029 in an XmText > > widget that is exactly 5 characters wide. In a select statement like: > > SELECT to_char (1029, '9,999'), to_char always puts one extra space in > > the leftmost position for a numeric sign. What I get is: ' 1,029', which > > causes > > me to have to have the XmText widget 6 characters wide as opposed to 5. > > This > > is becoming a problem on several of the application windows where, as I > > said > > earlier, space is at a premium. The only way that I've been able to > > suppress > > the extra space is by the following: SELECT to_char (1029, 'FM9,999'). > > What I > > get then is a string that is not right justified and I want the numbers > > to be > > right justified. Now, finally, the question: Is there a way to suppress > > the > > extra space for the sign? > > > > TIA > > rnd=# select version(); > > version > > ------------------------------------------------------------------------- > >------------------------------------- PostgreSQL 7.4.6 on > > i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 > > 20030502 (Red Hat Linux 3.2.3-49) > > (1 row) > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faq > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq -- Quote: 4 "There is a rank due to the United States, among nations, which will be withheld, if not absolutely lost, by the reputation of weakness. If we desire to avoid insult, we must be able to repel it; if we desire to secure peace, one of the most powerful instruments of our rising prosperity, it must be known that we are at all times ready for war." --George Washington Work: 1-336-372-6812 Cell: 1-336-363-4719 email: terry@esc1.com
On Tue, Dec 13, 2005 at 11:30:36 -0500, Terry Lee Tucker <terry@esc1.com> wrote: > > On Tuesday 13 December 2005 11:20 am, John Sidney-Woollett saith: > > Not sure if there is a numeric formatting option that allows what you want. > > > > But how about? > > > > substr(to_char(1029, '9,999'),2) > > That's so simple, I'm embarrased ;o) > > Thanks for the help... You can also us 'FM' to get rid of extra space. From the to_char docs: FM suppresses leading zeroes and trailing blanks that would otherwise be added to make the output of a pattern be fixed-width.
Bruno, Thanks for the response. The only problem is that FM removes all the leading spaces. I may have been unclear in stating my problem. I want the padding on the left; however, I don't want the extra space for the sign (+,-) that gets prepended to the string. This output shows what I mean: rnd=# select to_char(1234, '"|"9999"|"'); to_char --------- | 1234| (1 row) As you can see, not only do I get the four spaces I wanted, but I get one additional. That is what I was trying to get rid of and John's solution worked perfectly. It takes the extra space at the beginning out. I have a couple overloaded functions handling this as well as ensuring that I'm not dropping the negative sign on a number that actually is negative. Anyway, thanks again for the response... On Wednesday 14 December 2005 01:09 pm, Bruno Wolff III saith: > On Tue, Dec 13, 2005 at 11:30:36 -0500, > > Terry Lee Tucker <terry@esc1.com> wrote: > > On Tuesday 13 December 2005 11:20 am, John Sidney-Woollett saith: > > > Not sure if there is a numeric formatting option that allows what you > > > want. > > > > > > But how about? > > > > > > substr(to_char(1029, '9,999'),2) > > > > That's so simple, I'm embarrased ;o) > > > > Thanks for the help... > > You can also us 'FM' to get rid of extra space. From the to_char docs: > FM suppresses leading zeroes and trailing blanks that would otherwise be > added to make the output of a pattern be fixed-width. > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster