Message

Subject: Looks are important Date: Wed, 12 Nov 2003 16:12:28 -0600 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00A9_01C3A937.C4A6B160" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.4 tagged_above=0.0 required=5.0 tests=HTML_60_70, HTML_FONT_COLOR_BLUE, HTML_FONT_COLOR_GREEN X-Spam-Level: X-Archive-Number: 200311/143 X-Sequence-Number: 15958 This is a multi-part message in MIME format. ------=_NextPart_000_00A9_01C3A937.C4A6B160 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi Everyone, I am trying to concatenate two fields through a query: SELECT RPAD(no,30,' ') || tableb.kind FROM tablea=20 WHERE tablea.kind =3D 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_00A9_01C3A937.C4A6B160 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi Everyone,

I am trying to concatenate two fields thro= ugh a=20 query:

SELECT= =20 RPAD(no,30,' ') || tableb.kind FROM <= FONT=20 color=3D#008000>tablea

WHERE=20 tablea.kind =3D tableb.kind

The result gives (for=20 example):

4595448          = ;            = =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

What I would like to have i= s better=20 alignment:

4595448          = ;           Green5966           &nbs= p;            &= nbsp;=20 Yellow
106-60033         &n= bsp;        Green
15-94-387 = ;            &n= bsp;     Red
217-991173    =             &nb= sp;Blue

Is there some kind of encoding or other st= ring=20 options that will result in better alignment than what I've tried with=20 Rpad?

Thanks in advance,

George

------=_NextPart_000_00A9_01C3A937.C4A6B160-- From lcofield@box-works.com Wed Nov 12 23:21:06 2003 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 A7CCED1B502 for ; Wed, 12 Nov 2003 23:21:06 +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 95672-01 for ; Wed, 12 Nov 2003 19:20:38 -0400 (AST) Received: from ns.lanterm.net (unknown [208.187.178.11]) by svr1.postgresql.org (Postfix) with ESMTP id 89D7CD1B53F for ; Wed, 12 Nov 2003 19:20:01 -0400 (AST) Received: from Louise ([208.187.178.2]) by ns.lanterm.net (8.11.6/8.11.6) with ESMTP id hACLwks09068; Wed, 12 Nov 2003 14:58:46 -0700 From: "Louise Cofield" To: "'George Weaver'" , Subject: Re: Looks are important Date: Wed, 12 Nov 2003 16:19:43 -0700 Message-ID: <003a01c3a973$743eed00$7801a8c0@Louise> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_003B_01C3A938.C7E01500" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.3416 In-Reply-To: <00ac01c3a96a$a165b290$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/145 X-Sequence-Number: 15960 This is a multi-part message in MIME format. ------=_NextPart_000_003B_01C3A938.C7E01500 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit 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_003B_01C3A938.C7E01500 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable Message

Try the TRIM funct= ion or=20 the LTRIM function:

&nbs= p;

SELECT RPAD(no,30,' ') || 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 = 3:12=20 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Looks = are=20 important

Hi Everyone,

I am trying to concatenate two fields th= rough a=20 query:

SELECT=20 RPAD(no,30,' ') || tableb.kind FROM = ;tablea

WHERE=20 tablea.kind =3D tableb.kind

The result gives (for=20 example):

4595448         &nb= sp;            = =20 Green
5966          =             &nb= sp;  =20 Yellow
106-60033         =            =20 Green
15-94-387         &= nbsp;          =20 Red
217-991173         &n= bsp;         =20 Blue

What I would like to have= is better=20 alignment:

4595448         &nb= sp;           Green<= BR>5966           &n= bsp;            = ; =20 Yellow
106-60033         =          Green
15-94-387&nb= sp;            =       Red
217-991173   &nbs= p;            &= nbsp;Blue

Is there some kind of encoding or other = string=20 options that will result in better alignment than what I've tried with=20 Rpad?

Thanks in advance,

George

------=_NextPart_000_003B_01C3A938.C7E01500-- From josh@agliodbs.com Wed Nov 12 23:59:36 2003 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 BF5DCD1B53F for ; Wed, 12 Nov 2003 23:59:36 +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 87624-09 for ; Wed, 12 Nov 2003 19:59:07 -0400 (AST) Received: from davinci.ethosmedia.com (server228.ethosmedia.com [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id 5323FD1B556 for ; Wed, 12 Nov 2003 19:59:01 -0400 (AST) Received: from [66.219.92.2] (HELO temoku) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3907702; Wed, 12 Nov 2003 15:59:29 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: "George Weaver" , Subject: Re: Looks are important Date: Wed, 12 Nov 2003 15:56:12 -0800 User-Agent: KMail/1.4.3 References: <00ac01c3a96a$a165b290$6400a8c0@Dell4500> In-Reply-To: <00ac01c3a96a$a165b290$6400a8c0@Dell4500> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200311121556.12887.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/144 X-Sequence-Number: 15959 George, > SELECT RPAD(no,30,' ') || tableb.kind FROM tablea=20 > WHERE tablea.kind =3D tableb.kind Try SELECT RPAD(no, (35 - LENGTH(tableb.kind)), ' ') --=20 -Josh Berkus Aglio Database Solutions San Francisco From georgew1@mts.net Thu Nov 13 03:15:44 2003 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 EBE4FD1B4F2 for ; Thu, 13 Nov 2003 03:15:44 +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 28880-10 for ; Wed, 12 Nov 2003 23:15:17 -0400 (AST) Received: from smtp2.mts.net (smtp2.mts.net [205.200.16.113]) by svr1.postgresql.org (Postfix) with ESMTP id BA232D1B54F for ; Wed, 12 Nov 2003 23:15:12 -0400 (AST) Received: from Dell4500 (wnpgmb01bbz-ac04-12-178.dial.mts.net [142.161.12.178]) by smtp2.mts.net (8.12.8-20030918/8.11.3) with SMTP id hAD3F9BJ009603; Wed, 12 Nov 2003 21:15:10 -0600 (CST) Message-ID: <002c01c3a994$5ba611d0$6400a8c0@Dell4500> From: "George Weaver" To: "Josh Berkus" , , "Louise Cofield" References: <003a01c3a973$743eed00$7801a8c0@Louise> Subject: Re: Looks are important Date: Wed, 12 Nov 2003 21:14:34 -0600 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0029_01C3A961.F8945B60" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/146 X-Sequence-Number: 15961 This is a multi-part message in MIME format. ------=_NextPart_000_0029_01C3A961.F8945B60 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable MessageHi Louise, Josh, Thanks for the suggestions. What I'm trying to accomplish is to have a space between no and kind. Leng= th(no) can vary. I would like all the kinds to line up evenly when display= ed, with a space between no and kind. But when I RPAD no (to try and get a= n 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 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. Is what I'm trying to do possible??? George ----- Original Message -----=20 From: Louise Cofield=20 To: 'George Weaver' ; pgsql-sql@postgresql.org=20 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=20 WHERE tablea.kind =3D 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=20 WHERE tablea.kind =3D 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_0029_01C3A961.F8945B60 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Message

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 the = kinds=20 to line up evenly when displayed, with a space between no and kind.&nb= sp;=20 But when I RPAD no (to try and get an even starting point for kind), &= nbsp;=20 the ' 's are not quite the same width as an ordinary number or letter. = ;=20 Thus the physical display length of "30 characters" (padded) can vary from = row=20 to row. The result is that the kinds don't necessary line up neatly. = I=20 need to concatenate the two as they are being displayed as one column in a = drop=20 down combobox.

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

George

----- Original Message -----
Fro= m:=20 = Louise=20 Cofield

To: 'George Weaver' ; pgsql-sql@postgresql.org

Sent: Wednesday, November 12, 2003= 5:19=20 PM

Subject: RE: [SQL] Looks are=20 important

Try the TRIM fun= ction or=20 the LTRIM function:

SELECT RPAD(no,30,' ') || TRIM(tableb.kind) FROM&nbs= p;tablea

WHERE tablea.kind =3D tableb.kind

Louise

<= FONT=20 face=3DTahoma size=3D2>-----Original Message-----
From: pgsql-sql-owner@postgres= ql.org=20 [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of George=20 Weaver
Sent: Wednesday, November 12, 2003 3:12 PM
To: pgsql-sql@postgresql.orgSubject:=20 [SQL] Looks are important

Hi Everyone,

I am trying to concatenate two fields = through a=20 query:

SELECT=20 RPAD(no,30,' ') || tableb.kind FROM&nb= sp;tablea

WHERE tablea.kind =3D=20 tableb.kind

The result gives (for= =20 example):

4595448         &= nbsp;           &nbs= p;=20 Green
5966         &nbs= p;            &= nbsp;  =20 Yellow
106-60033        &nbs= p;           =20 Green
15-94-387         = ;           =20 Red
217-991173         =           =20 Blue

What I would like to ha= ve is=20 better alignment:

4595448         &= nbsp;           Gree= n
5966           =             &nb= sp; =20 Yellow
106-60033        &nbs= p;         Green
15-94-387&= nbsp;           &nbs= p;      Red
217-991173   &n= bsp;            = ; Blue

Is there some kind of encoding or othe= r string=20 options that will result in better alignment than what I've tried with= =20 Rpad?

Thanks in advance,

George

------=_NextPart_000_0029_01C3A961.F8945B60-- From tgl@sss.pgh.pa.us Thu Nov 13 03:31:54 2003 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 354F7D1B51A for ; Thu, 13 Nov 2003 03:31:54 +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 39081-06 for ; Wed, 12 Nov 2003 23:31:27 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 3B491D1B4FA for ; Wed, 12 Nov 2003 23:31:23 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id hAD3VJ19018608; Wed, 12 Nov 2003 22:31:19 -0500 (EST) To: "George Weaver" Cc: "Josh Berkus" , pgsql-sql@postgresql.org, "Louise Cofield" Subject: Re: Looks are important In-reply-to: <002c01c3a994$5ba611d0$6400a8c0@Dell4500> References: <003a01c3a973$743eed00$7801a8c0@Louise> <002c01c3a994$5ba611d0$6400a8c0@Dell4500> Comments: In-reply-to "George Weaver" message dated "Wed, 12 Nov 2003 21:14:34 -0600" Date: Wed, 12 Nov 2003 22:31:18 -0500 Message-ID: <18607.1068694278@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/147 X-Sequence-Number: 15962 "George Weaver" 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 From georgew1@mts.net Thu Nov 13 13:30:36 2003 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 4FE15D1B522 for ; Thu, 13 Nov 2003 13:30:36 +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 22916-09 for ; Thu, 13 Nov 2003 09:30:08 -0400 (AST) Received: from smtp2.mts.net (smtp2.mts.net [205.200.16.113]) by svr1.postgresql.org (Postfix) with ESMTP id 295D5D1B515 for ; Thu, 13 Nov 2003 09:30:04 -0400 (AST) Received: from Dell4500 (wnpgmb01ba1-ac05-15-127.dial.mts.net [142.161.15.127]) by smtp2.mts.net (8.12.8-20030918/8.11.3) with SMTP id hADDU1BJ026430; Thu, 13 Nov 2003 07:30:02 -0600 (CST) Message-ID: <001a01c3a9ea$42832c00$6400a8c0@Dell4500> From: "George Weaver" To: "Tom Lane" Cc: "Josh Berkus" , , "Louise Cofield" References: <003a01c3a973$743eed00$7801a8c0@Louise> <002c01c3a994$5ba611d0$6400a8c0@Dell4500> <18607.1068694278@sss.pgh.pa.us> Subject: Re: Looks are important Date: Thu, 13 Nov 2003 07:29:31 -0600 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/151 X-Sequence-Number: 15966 Hi Tom, Switching to a fixed-width font did the trick. Thanks for the help. George ----- Original Message ----- From: "Tom Lane" To: "George Weaver" Cc: "Josh Berkus" ; ; "Louise Cofield" Sent: Wednesday, November 12, 2003 9:31 PM Subject: Re: [SQL] Looks are important > "George Weaver" 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) > From lcofield@box-works.com Thu Nov 13 15:23:07 2003 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--