X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 2B13BD1B4FE for ; Thu, 13 Nov 2003 15:23:07 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 60790-05 for ; Thu, 13 Nov 2003 11:22:40 -0400 (AST) Received: from ns.lanterm.net (unknown [208.187.178.11]) by svr1.postgresql.org (Postfix) with ESMTP id 6F693D1B51E for ; Thu, 13 Nov 2003 11:22:30 -0400 (AST) Received: from Louise ([208.187.178.2]) by ns.lanterm.net (8.11.6/8.11.6) with ESMTP id hADE1Gs18578; Thu, 13 Nov 2003 07:01:16 -0700 From: "Louise Cofield" To: "'George Weaver'" , "'Josh Berkus'" , Subject: Re: Looks are important Date: Thu, 13 Nov 2003 08:22:33 -0700 Message-ID: <003401c3a9f9$f63fe800$7801a8c0@Louise> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0035_01C3A9BF.49E11000" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.3416 In-Reply-To: <002c01c3a994$5ba611d0$6400a8c0@Dell4500> Importance: Normal X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/154 X-Sequence-Number: 15969 This is a multi-part message in MIME format. ------=_NextPart_000_0035_01C3A9BF.49E11000 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit 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 ----- From: Louise Cofield To: 'George Weaver' ; pgsql-sql@postgresql.org 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 ------=_NextPart_000_0035_01C3A9BF.49E11000 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable Message
Using=20 a fixed-width font as Tom suggests, concatenate an additional space between= the=20 no field and the kind field:
 
SELECT RPAD(no,30,' ') || ' '=20 || TRIM(tableb.kind) FROM = tablea 
WHERE tablea.kind =3D tableb.kind
 
Louise
-----Original Message-----
From:=20 pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On=20 Behalf Of George Weaver
Sent: Wednesday, November 12, 2003 = 8:15=20 PM
To: Josh Berkus; pgsql-sql@postgresql.org; Louise=20 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=20 between no and kind.  Length(no) can vary.  I would like all th= e=20 kinds to line up evenly when displayed, with a space between no and= =20 kind.  But when I RPAD no (to try and get an even starting point for= =20 kind),   the ' 's are not quite the same width as an ordinary n= umber=20 or letter.  Thus the physical display length of "30 characters" (pad= ded)=20 can vary from row to row. The result is that the kinds don't necessary li= ne up=20 neatly.  I need to concatenate the two as they are being displayed a= s one=20 column in a drop down combobox.
 
Is what I'm trying to do possible???
 
George
----- Original Message -----
F= rom:=20 Louise=20 Cofield
To: 'George Weaver' ; pgsql-sql@postgresql.org <= /DIV>
Sent: Wednesday, November 12, 20= 03 5:19=20 PM
Subject: RE: [SQL] Looks are=20 important

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

Hi Everyone,
 
I am trying to concatenate two field= s through=20 a query:
 
SELECT=20 RPAD(no,30,' ') || tableb.kind FROM&= nbsp;tablea 
WHERE tablea.kind =3D=20 tableb.kind
 <= /DIV>
The result gives (for= =20 example):
 <= /DIV>
4595448         = ;            &n= bsp;=20 Green
5966         &n= bsp;            = ;   =20 Yellow
106-60033        &n= bsp;           =20 Green
15-94-387        &nb= sp;           =20 Red
217-991173        &nbs= p;          =20 Blue
 <= /DIV>
What I would like to = have is=20 better alignment:
 <= /DIV>
4595448         = ;            Gr= een
5966          &nbs= p;            &= nbsp; =20 Yellow
106-60033        &n= bsp;         Green
15-94-38= 7            &n= bsp;      Red
217-991173   =             &nb= sp; Blue
 
Is there some kind of encoding or ot= her=20 string options that will result in better alignment than what I've tr= ied=20 with Rpad?
 
Thanks in advance,
George
 
 
 <= /DIV>
 
------=_NextPart_000_0035_01C3A9BF.49E11000--