Thread: How to get normalized data from tekst column

How to get normalized data from tekst column

From
"Andrus"
Date:
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

Re: How to get normalized data from tekst column

From
David Johnston
Date:
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.

Re: How to get normalized data from tekst column

From
"Andrus"
Date:
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.

Re: How to get normalized data from tekst column

From
Harald Fuchs
Date:
"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.

Re: How to get normalized data from tekst column

From
"Andrus Moor"
Date:
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.

Re: How to get normalized data from tekst column

From
David Johnston
Date:
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
seemsproblematic since functions like substring cannot do that. 

David J.

Re: How to get normalized data from tekst column

From
"Andrus"
Date:
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.