From ssmani@stockholding.com Mon Nov 29 09:13:10 1999 Received: from cluster1.vsnl.net.in (cluster1.vsnl.net.in [202.54.1.66]) by hub.org (8.9.3/8.9.3) with ESMTP id EAA79315 for ; Mon, 29 Nov 1999 04:13:10 -0500 (EST) (envelope-from ssmani@stockholding.com) Received: from mailserver ([202.54.22.166]) by cluster1.vsnl.net.in (8.8.8/8.8.8) with SMTP id OAA19514 for ; Mon, 29 Nov 1999 14:44:09 -0500 (GMT) Received: by mailserver with XtraMail-SMTP/POP3-Server (v1.2 48210005891) for at Mon, 29 Nov 1999 14:54:29 +0530 Message-ID: <3842440B.2AA9E04F@stockholding.com> Date: Mon, 29 Nov 1999 14:44:51 +0530 From: S S Mani X-Mailer: Mozilla 4.05 [en] (Win95; I) MIME-Version: 1.0 To: jt-kirkpatrick@mpsllc.com, pgsql-sql@hub.org, djackson@cpsgroup.com Subject: Your query ... Content-Type: multipart/alternative; boundary="------------45468F27C5551F9BAAFE37F1" --------------45468F27C5551F9BAAFE37F1 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi Maybe your query has already been solved! If not then pl. try it here :- The solution given by Jackson DeJuan will work if the datatypes & parameters of both fields are the same. If they are not then try :- select subnum||' ' /* The no. of spaces between the single quotes should be equal to length of col vnum */ from subs union select ' '||vnum /* The no. of spaces between the single quotes should be equal to length of col subnum */ from vend =======================**************======================================= > RE: [SQL] combining a single field in two tables into one column. > > > > From: "Jackson, DeJuan" > To: JT Kirkpatrick , "'pgsql-sql@hub.org'" > Subject: RE: [SQL] combining a single field in two tables into > one column. > Date: Fri, 30 Apr 1999 11:02:47 -0500 > > > > try: > select subnum from subs > union all > select vnum from vend; > -DEJ > > > -----Original Message----- > > From: JT Kirkpatrick [SMTP:jt-kirkpatrick@mpsllc.com] > > Sent: Friday, April 30, 1999 10:59 AM > > To: 'pgsql-sql@hub.org' > > Subject: [SQL] combining a single field in two tables into one > > column. > > > > i come seeking wisdom. . . > > > > i have two tables, one for subcontractors and another for vendors. > subs > > have a primary key of subnum int4, vendors a primary key of vnum > int4. i > > need to create a query that allows me to see a complete list of > these > > numbers in a single column. for instance, subnum (all begin with 9) > has > > values 90001, 90002, 90003 (only three records for the example), and > vnum > > (all begin with 2) has values 20001, 20002. i'd like my query > result to > > show: > > > > 90001 > > 90002 > > 90003 > > 20001 > > 20002 > > > > this does NOT work: select a.subnum as z, b.vnum as z from subs a, > vend > > b; > > NOR does: select a.subnum, b.vnum from subs a, vend b; > > > > by the way, the two tables DO NOT relate to each other (i.e., 90001 > does > > not have anything to do with 20001). . . > > > > TIA, jt > > --------------45468F27C5551F9BAAFE37F1 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Hi
Maybe your query has already been solved!
If not then pl. try it here :-
The solution given by Jackson DeJuan will work if the datatypes & parameters of both fields are the same.
If they are not then try :-

select subnum||'       ' /* The no. of spaces between the single quotes should be equal to length of col vnum */
from subs
union
select '          '||vnum  /* The no. of spaces between the single quotes should be equal to length of col subnum */
from vend
 =======================**************=======================================
 

RE: [SQL] combining a single field in two tables into one column.



     From: "Jackson, DeJuan"  
     To: JT Kirkpatrick , "'pgsql-sql@hub.org'"  
     Subject: RE: [SQL] combining a single field in two tables into one column. 
     Date: Fri, 30 Apr 1999 11:02:47 -0500 



try:
select subnum from subs
union all
select vnum from vend;
        -DEJ

> -----Original Message-----
> From: JT Kirkpatrick [SMTP:jt-kirkpatrick@mpsllc.com]
> Sent: Friday, April 30, 1999 10:59 AM
> To:   'pgsql-sql@hub.org'
> Subject:      [SQL] combining a single field in two tables into one
> column.
> 
> i come seeking wisdom. . .
> 
> i have two tables, one for subcontractors and another for vendors.  subs 
> have a primary key of subnum int4, vendors a primary key of vnum int4.  i 
> need to create a query that allows me to see a complete list of these 
> numbers in a single column.  for instance, subnum (all begin with 9) has 
> values 90001, 90002, 90003 (only three records for the example), and vnum 
> (all begin with 2) has values 20001, 20002.  i'd like my query result to 
> show:
> 
> 90001
> 90002
> 90003
> 20001
> 20002
> 
> this does NOT work:  select a.subnum as z, b.vnum as z from subs a, vend
> b;
> NOR does: select a.subnum, b.vnum from subs a, vend b;
> 
> by the way, the two tables DO NOT relate to each other (i.e., 90001 does 
> not have anything to do with 20001). . .
> 
> TIA, jt


--------------45468F27C5551F9BAAFE37F1-- From ssmani@stockholding.com Mon Nov 29 09:30:53 1999 Received: from cluster1.vsnl.net.in (cluster1.vsnl.net.in [202.54.1.66]) by hub.org (8.9.3/8.9.3) with ESMTP id EAA81596 for ; Mon, 29 Nov 1999 04:30:53 -0500 (EST) (envelope-from ssmani@stockholding.com) Received: from mailserver ([202.54.22.166]) by cluster1.vsnl.net.in (8.8.8/8.8.8) with SMTP id PAA27383 for ; Mon, 29 Nov 1999 15:02:01 -0500 (GMT) Received: by mailserver with XtraMail-SMTP/POP3-Server (v1.2 48210005891) for at Mon, 29 Nov 1999 15:07:57 +0570 Message-ID: <38424734.8A549E4D@stockholding.com> Date: Mon, 29 Nov 1999 14:58:20 +0530 From: S S Mani X-Mailer: Mozilla 4.05 [en] (Win95; I) MIME-Version: 1.0 To: nuch@valigene.com, pgsql-sql@hub.org, michael.j.davis@tvguide.com Subject: Your Query... Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi You may have already got a solution. But the most straightforward soln. would be :- SELECT substr(a,1,instr(a,'_')-1) FROM .... < From first field to the filed before the first appearance of '_'> Pl. confirm whether this works. Bye S.S.Mani ************************************************************** RE: [SQL] substring From: Michael J Davis To: "'Nuchanard Chiannilkulchai'" , pgsql-sql@hub.org Subject: RE: [SQL] substring Date: Mon, 26 Apr 1999 14:35:16 -0500 try: select a, substring(a, 1, position('_' in a) -1) from table_a; I have not tested this. Not sure if the parameters to substring are correct. Also not sure if the -1 is needed. > -----Original Message----- > From: Nuchanard Chiannilkulchai [SMTP:nuch@valigene.com] > Sent: Monday, April 26, 1999 8:57 AM > To: pgsql-sql@hub.org > Subject: [SQL] substring > > Hello, > > How should I do my query to put a substring value in a field, in > postgres ( I have 6.4) ? > > I have in table table_a > a > ---------------- > 98-004_c136 > P124_154 > 98-005_c171 > > and i want to select only the text before the underscore: > > in sybase, this should be > select a, substring(a,1,charindex('_',a)-1) from table_a > a > ---------------- ---------------- > 98-004_c136 98-004 > 98-005_c171 98-005 > P124_154 P124 > > Thanks a lot, > Nuch >