Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f5SCYsa92388 for ; Thu, 28 Jun 2001 08:34:54 -0400 (EDT) (envelope-from pgsql-sql-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5RFW4a42461 for ; Wed, 27 Jun 2001 11:32:04 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id f5RFODh24875 for pgsql-sql@postgresql.org; Wed, 27 Jun 2001 11:24:13 -0400 (EDT) (envelope-from news) From: jim_esti@hotmail.com (Jim) X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Text manipulation in SQL Date: 27 Jun 2001 08:24:06 -0700 Organization: http://groups.google.com/ Lines: 47 Message-ID: References: Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: groups-support@google.com To: pgsql-sql@postgresql.org X-Archive-Number: 200106/444 X-Sequence-Number: 3741 Presto, Figured it out. Select (reverse(substr(reverse(trim(NAME)), (INSTR(reverse(trim(_NAME)), ' ', 1, 1)), (length(reverse(trim(_NAME))))))) as LASTNAME, (substr(trim(NAME), (length(trim(NAME))-(INSTR(reverse(trim(NAME)), ' ', 1, 1)))+2, (length(trim(NAME))))) as FIRSTNAME From blabla_table; It may look nasty but it does do exacty what was required. jim_esti@hotmail.com (Jim) wrote in message news:... > Hi All, > > I have a column with a variety of names in it, for example > John, Smith > John, A S > Jane, Doe A > Jane, A > > I will call this column NAMES for this. > I was looking to manipulate that column. > Specifically: > The NAME field will be split after the rightmost blank. Letter on the > right of that blank will be displayed as LAST_NAME, the remainder will > be displayed as FIRST_NAME. > > Does anyone know how to do this correctly? > > I have tried something like this: > LTRIM( NAME, (substr(NAME,1,(INSTR(NAME,' ',1,1))))) > But that would only yield something like this: > John, > John, > Jane, > Jane, > > My little bit of code seems to only extract up to the first blank > space. Which would only work correctly if the names where only in two > parts (like Jane, A). > As I said I would like to return the NAME column in two parts (the > letters to the right of the right most blank, and the other half). > It seems a little trick to me. > Anyone have any advice, hints, or solutions?