How to get normalized data from tekst column

On Nov 24, 2011, at 8:47, "Andrus" <kobruleht2@hot.ee> wrote:

Project table contains salesman names and percents as shown
below. Single comment column contains 1-2 salesman names and commissions.
How select normalized data from this table ?

Andrus.

CREATE TABLE project (
id char(10) primary key,
comment char(254)
);

insert into test values ('2010-12', 'Aavo 19%, Peedu 15%');
insert into test values ('2010-22', 'Lauri-21%,Peedu 15%');
insert into test values ('2011-33', 'Taavi 21%');

How to create select statement in Postgresql 8.1.23 which
selects this data as normalized table like

CREATE TABLE commission (
projectid char(10),
salesman char(5),
commission n(2) )

result using data above should be

'2010-12', 'Aavo', 19
'2010-12', 'Peedu', 15
'2010-22', 'Lauri', 21
'2010-22', 'Peedu', 15
'2011-33', 'Taavi', 21

Regular Expressions are your friend here. If you do not know them you should learn them; though if you ask nicely someone may just provide you the solution you need.

Split-to-array and unnest may work as well.

All this said, you are currently using an unsupported version of PostgreSQL and I do not know what specific functionality you have to work with.

David J.

David,

Regular Expressions are your friend here. If you do not know them you should learn them; though if you ask nicely someone may just provide you the solution you need.
Split-to-array and unnest may work as well.

Thank you very much. I don’t know regexps.
Can you provide example, please for 8.1. Or maybe CASE WHEN and substring testing can also used.

Andrus.

"Andrus" <kobruleht2@hot.ee> writes:

David,

Regular Expressions are your friend here. If you do not know them you

should learn them; though if you ask nicely someone may just provide you
the solution you need.

Split-to-array and unnest may work as well.

Thank you very much. I dona**t know regexps.
Can you provide example, please for 8.1. Or maybe CASE WHEN and substring
testing can also used.

The query

SELECT id, a[1] AS name, a[2] AS percent
FROM (
SELECT id, regexp_split_to_array(regexp_split_to_table(comment, ', *'), '\W+') AS a
FROM project
) AS dummy

should work un every halfway recent PostgreSQL version - dunno about 8.1.

Harald,

Thank you.

The query

SELECT id, a[1] AS name, a[2] AS percent
FROM ( SELECT id, regexp_split_to_array(regexp_split_to_table(comment, ',
*'), '\W+') AS a
FROM project ) AS dummy

should work un every halfway recent PostgreSQL version - dunno about 8.1.

I tried it but got error in 8.1:

ERROR: function regexp_split_to_table(text, "unknown") does not exist
HINT: No function matches the given name and argument types. You may need
to add explicit type casts.

how to get data in 8.1 ?

Andrus.

On Nov 24, 2011, at 15:40, "Andrus Moor" <eetasoft@online.ee> wrote:

Harald,

Thank you.

The query

SELECT id, a[1] AS name, a[2] AS percent
FROM ( SELECT id, regexp_split_to_array(regexp_split_to_table(comment, ', *'), '\W+') AS a
FROM project ) AS dummy

should work un every halfway recent PostgreSQL version - dunno about 8.1.

I tried it but got error in 8.1:

ERROR: function regexp_split_to_table(text, "unknown") does not exist
HINT: No function matches the given name and argument types. You may need to add explicit type casts.

how to get data in 8.1 ?

Andrus.

You seem to have 2 options:

1. Upgrade to at least 8.4 and use the regexp functions.
2. Write something in PL/pgsql

Whether you can write a sufficient function with 8.1 features I do not know.

You main issue is you need to be able to output multiple records from a single input record and doing so before 8.4 seems problematic since functions like substring cannot do that.

David J.

David,

Thank you.

Whether you can write a sufficient function with 8.1 features I do not
know.
You main issue is you need to be able to output multiple records from a
single input record and doing so before 8.4 seems problematic since
functions like substring cannot do that.

comment field contain 0.. 2 salemans, no more:

'Aavo 19%, Peedu 15%'
'Lauri-21%,Peedu 15%'
'Taavi 21%'

Maybe in 8.1 it is possible to write 2 select statements. First will extract
first item and second select
will extract second item if second item exists ?

Andrus.