Thread: How to get normalized data from tekst column

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

"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
seemsproblematic 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.