Postgres Pro
Downloads
Home   >   mailing lists

Thread: Looks are important

Looks are important

From
"George Weaver"
Date:
Hi Everyone,
 
I am trying to concatenate two fields through a query:
 
SELECT RPAD(no,30,' ') || tableb.kind FROM tablea 
WHERE tablea.kind = tableb.kind
 
The result gives (for example):
 
4595448                       Green
5966                          Yellow
106-60033                     Green
15-94-387                     Red
217-991173                    Blue
 
What I would like to have is better alignment:
 
4595448                     Green
5966                          Yellow
106-60033                  Green
15-94-387                   Red
217-991173                 Blue
 
Is there some kind of encoding or other string options that will result in better alignment than what I've tried with Rpad?
 
Thanks in advance,
George
 
 
 
 

Re: Looks are important

From
Josh Berkus
Date:
George,

> SELECT RPAD(no,30,' ') || tableb.kind FROM tablea
> WHERE tablea.kind = tableb.kind

Try SELECT RPAD(no, (35 - LENGTH(tableb.kind)), ' ')

--
-Josh BerkusAglio Database SolutionsSan Francisco

Re: Looks are important

From
"Louise Cofield"
Date:
Try the TRIM function or the LTRIM function:
 
SELECT RPAD(no,30,' ') || TRIM(tableb.kind) FROM tablea 
WHERE tablea.kind = tableb.kind
 
Louise
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of George Weaver
Sent: Wednesday, November 12, 2003 3:12 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Looks are important

Hi Everyone,
 
I am trying to concatenate two fields through a query:
 
SELECT RPAD(no,30,' ') || tableb.kind FROM tablea 
WHERE tablea.kind = tableb.kind
 
The result gives (for example):
 
4595448                       Green
5966                          Yellow
106-60033                     Green
15-94-387                     Red
217-991173                    Blue
 
What I would like to have is better alignment:
 
4595448                     Green
5966                          Yellow
106-60033                  Green
15-94-387                   Red
217-991173                 Blue
 
Is there some kind of encoding or other string options that will result in better alignment than what I've tried with Rpad?
 
Thanks in advance,
George
 
 
 
 

Re: Looks are important

From
"George Weaver"
Date:
Hi Louise, Josh,
 
Thanks for the suggestions.
 
What I'm trying to accomplish is to have a space between no and kind.  Length(no) can vary.  I would like all the kinds to line up evenly when displayed, with a space between no and kind.  But when I RPAD no (to try and get an even starting point for kind),   the ' 's are not quite the same width as an ordinary number or letter.  Thus the physical display length of "30 characters" (padded) can vary from row to row. The result is that the kinds don't necessary line up neatly.  I need to concatenate the two as they are being displayed as one column in a drop down combobox.
 
Is what I'm trying to do possible???
 
George
----- Original Message -----
Sent: Wednesday, November 12, 2003 5:19 PM
Subject: RE: [SQL] Looks are important

Try the TRIM function or the LTRIM function:
 
SELECT RPAD(no,30,' ') || TRIM(tableb.kind) FROM tablea 
WHERE tablea.kind = tableb.kind
 
Louise
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of George Weaver
Sent: Wednesday, November 12, 2003 3:12 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Looks are important

Hi Everyone,
 
I am trying to concatenate two fields through a query:
 
SELECT RPAD(no,30,' ') || tableb.kind FROM tablea 
WHERE tablea.kind = tableb.kind
 
The result gives (for example):
 
4595448                       Green
5966                          Yellow
106-60033                     Green
15-94-387                     Red
217-991173                    Blue
 
What I would like to have is better alignment:
 
4595448                     Green
5966                          Yellow
106-60033                  Green
15-94-387                   Red
217-991173                 Blue
 
Is there some kind of encoding or other string options that will result in better alignment than what I've tried with Rpad?
 
Thanks in advance,
George
 
 
 
 

Re: Looks are important

From
Tom Lane
Date:
"George Weaver" <georgew1@mts.net> writes:
> ... the ' 's are not quite the same width as=
>  an ordinary number or letter.  Thus the physical display length of "30 cha=
> racters" (padded) can vary from row to row. The result is that the kinds do=
> n't necessary line up neatly.  I need to concatenate the two as they are be=
> ing displayed as one column in a drop down combobox.

Use a fixed-width font.

> Is what I'm trying to do possible???

Not with a variable-width font that you haven't even told us the exact
character widths of ...
        regards, tom lane

Re: Looks are important

From
"George Weaver"
Date:
Hi Tom,

Switching to a fixed-width font did the trick.

Thanks for the help.

George

----- Original Message ----- 
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "George Weaver" <georgew1@mts.net>
Cc: "Josh Berkus" <josh@agliodbs.com>; <pgsql-sql@postgresql.org>; "Louise
Cofield" <lcofield@box-works.com>
Sent: Wednesday, November 12, 2003 9:31 PM
Subject: Re: [SQL] Looks are important


> "George Weaver" <georgew1@mts.net> writes:
> > ... the ' 's are not quite the same width as=
> >  an ordinary number or letter.  Thus the physical display length of "30
cha=
> > racters" (padded) can vary from row to row. The result is that the kinds
do=
> > n't necessary line up neatly.  I need to concatenate the two as they are
be=
> > ing displayed as one column in a drop down combobox.
>
> Use a fixed-width font.
>
> > Is what I'm trying to do possible???
>
> Not with a variable-width font that you haven't even told us the exact
> character widths of ...
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Re: Looks are important

From
"Louise Cofield"
Date:
Using a fixed-width font as Tom suggests, concatenate an additional space between the no field and the kind field:
 
SELECT RPAD(no,30,' ') || ' ' || TRIM(tableb.kind) FROM tablea 
WHERE tablea.kind = tableb.kind
 
Louise
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of George Weaver
Sent: Wednesday, November 12, 2003 8:15 PM
To: Josh Berkus; pgsql-sql@postgresql.org; Louise Cofield
Subject: Re: [SQL] Looks are important

Hi Louise, Josh,
 
Thanks for the suggestions.
 
What I'm trying to accomplish is to have a space between no and kind.  Length(no) can vary.  I would like all the kinds to line up evenly when displayed, with a space between no and kind.  But when I RPAD no (to try and get an even starting point for kind),   the ' 's are not quite the same width as an ordinary number or letter.  Thus the physical display length of "30 characters" (padded) can vary from row to row. The result is that the kinds don't necessary line up neatly.  I need to concatenate the two as they are being displayed as one column in a drop down combobox.
 
Is what I'm trying to do possible???
 
George
----- Original Message -----
Sent: Wednesday, November 12, 2003 5:19 PM
Subject: RE: [SQL] Looks are important

Try the TRIM function or the LTRIM function:
 
SELECT RPAD(no,30,' ') || TRIM(tableb.kind) FROM tablea 
WHERE tablea.kind = tableb.kind
 
Louise
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of George Weaver
Sent: Wednesday, November 12, 2003 3:12 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Looks are important

Hi Everyone,
 
I am trying to concatenate two fields through a query:
 
SELECT RPAD(no,30,' ') || tableb.kind FROM tablea 
WHERE tablea.kind = tableb.kind
 
The result gives (for example):
 
4595448                       Green
5966                          Yellow
106-60033                     Green
15-94-387                     Red
217-991173                    Blue
 
What I would like to have is better alignment:
 
4595448                     Green
5966                          Yellow
106-60033                  Green
15-94-387                   Red
217-991173                 Blue
 
Is there some kind of encoding or other string options that will result in better alignment than what I've tried with Rpad?
 
Thanks in advance,
George