Message-ID: <3DE640CE.D4335B14@rodos.fzk.de>
Date: Thu, 28 Nov 2002 17:14:06 +0100
From: Christoph Haller <ch@rodos.fzk.de>
MIME-Version: 1.0
To: mbedois@yahoo.com
Cc: pgsql-sql@postgresql.org
Subject: Re: retrieving specific info. from one column and locating it in
	another
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit

>
> I'm trying to retrieve some info from one column and
> put it in another.  I have a column that has a bunch
> of information in it called 'Route'.  I don't need to
> show all of that information.  Instead I need divide
> that single column into two seperate columns called
> 'Sender' and 'Receiver'.  How do I divide this
> information up into these two columns.  I know of
> methods called charindex and patindex.  I need to do
> something like that but instead of returning the
> position of the info, to just return the selected
> info.
> Ex)  I have a column named Route....with info in it
> similar to 'UPS NS  Ground'
>        How do I create a second column called
> 'Delivery' and pull only the 'NS' out of the Route
> column and put it into the 'Reciever' column?
>        Similarly how would I pull just the UPS part
> out of Route and put it into 'Sender'?
>

Marc,

I've seen some tricky stuff to split column values yesterday on the
list.
It was sent by Peter Childs Subject  Re: [SQL] Question on SQL and
pg_-tables.

He found something like a split.
Adapted to your needs it would result in something similar to (supposed
blanks are your separators)

SELECT SUBSTRING(route,1,POSITION(' ' IN route)-1) AS ups,
SUBSTRING(route,POSITION(' ' IN route)+1,POSITION(' ' IN
SUBSTRING(route,POSITION(' ' in ROUTE)+1))) AS ns
FROM ...

Hope this helps.

Regards, Christoph