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 >