From: "Jeff Eckermann" <jeff_eckermann@yahoo.com>
Subject: Re: Parsing a Calculation from a field
Date: Tue, 11 Jan 2005 08:50:52 -0600
Organization: Hub.Org Networking Services
Lines: 56
Message-ID: <cs0p2h$1e46$1@news.hub.org>
References: <6665151E3647D711B27B0090277C004F9AF681@ntexch02s.scs.dra.hmg.gb>
To: pgsql-sql@postgresql.org

How are you getting the data from Excel?  Perhaps you could use Excel's own 
methods to evaluate the cell contents?  You may still need to do something 
for literal text values (e.g. 'NULL'), though.


"Kieran Ashley" <krashley@space.qinetiq.com> wrote in message 
news:6665151E3647D711B27B0090277C004F9AF681@ntexch02s.scs.dra.hmg.gb...
Hi,

Apologies if this is the wrong list for this question, I hope it's not.

I'm porting an application to PostgreSQL, and rewriting a number of 
transforms used to convert incoming Excel data into a final Postgres table 
schema for an application.  Most of it's gone okay, but there's one column 
giving me trouble.

Some of our data comes in in a format which provides us with extra 
information, but which we currently don't need to use; specifically we 
sometimes receive information in the form of calculations, for example a 
column which needs to be transformed to an integer is initially of type 
varchar, and contains values like:

6
10
2
4 + 8
2 + 4 + 8
NULL
4

I need to come up with some code that can somehow treat the contents of this 
field as a potential sum, and execute that if necessary - something along 
the lines of 'select exec(FIELDNAME) from TABLE'.

I could obviously add an extra step to the procedure and write a script 
using something like PHP to scan the table and carry out any necessary 
calculations in advance, but I'm curious to know if there is a way within 
Postgres to do it either as part of the select query itself, or as a PL/SQL 
procedure.  I've had a quick look at the string handling functions but I can't 
see any way to parse integers out of a varchar column - which would seem to 
prohibit using substring to manually break up the sums.

Any help on this would be greatly appreciated.

Kieran


Note:
The information contained in this email and any subsequent correspondence is 
private and is intended solely for the intended recipient(s). For those 
other than the intended recipient(s) any disclosure, copying, distribution, 
or any action taken or omitted to be taken in reliance on such information 
is prohibited and may be unlawful.