pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: jim_esti@hotmail.com
To: pgsql-sql@postgresql.org
Subject: Re: Text manipulation in SQL
Date: 27 Jun 2001 08:24:06 -0700
Message-ID: <f0e3dc0b.0106270724.2d5f37dc@posting.google.com> (raw)
References: <f0e3dc0b.0106261359.7ab6b281@posting.google.com>

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:<f0e3dc0b.0106261359.7ab6b281@posting.google.com>...
> 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?



view thread (3+ messages)  latest in thread

Message-ID: <f0e3dc0b.0106270724.2d5f37dc@posting.google.com>
Permalink:  ../f0e3dc0b.0106270724.2d5f37dc@posting.google.com/
Also on:    postgresql.org/message-id/f0e3dc0b.0106270724.2d5f37dc@posting.google.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: jim_esti@hotmail.com
  Subject: Re: Text manipulation in SQL
  In-Reply-To: <f0e3dc0b.0106270724.2d5f37dc@posting.google.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox