Обсуждение: variables with SELECT statement

Kevin Duffy wrote:
> Within my table there is a field DESCRIPTION that I would like to parse 
> and split out into other fields.
> 
> Within DESCRIPTION there are spaces that separate the data items.  
> String_to_array(description, ‘  ‘) does the job very well.
> 
> I need something like this to work.
> 
>      select string_to_array(description, ' ') as a_desc,   
>             a_desc[0]  as name , a_desc[1]  as type,  a_desc[2]  as 
> size,  from prodlist where type = 'B'



You almost had it ...

select a_desc, a_desc[1] as name, a_desc[2] as type, a_desc[3] as size
from (select string_to_array(description, ' ') as a_desc from prodlist) 
as foo where a_desc[2] = 'B'

OK that is a syntax I have never seen.  But correct we are getting
close.

Noticed that string_to_array does not handle double spaces very well.
If there are double space between the tokens, there is "" (empty string)

in the array returned.  Not exactly what I expected.

KD

-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Frank Bax
Sent: Friday, September 05, 2008 4:07 PM
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] variables with SELECT statement

Kevin Duffy wrote:
> Within my table there is a field DESCRIPTION that I would like to
parse
> and split out into other fields.
>
> Within DESCRIPTION there are spaces that separate the data items.
> String_to_array(description, '  ') does the job very well.
>
> I need something like this to work.
>
>      select string_to_array(description, ' ') as a_desc,
>             a_desc[0]  as name , a_desc[1]  as type,  a_desc[2]  as
> size,  from prodlist where type = 'B'



You almost had it ...

select a_desc, a_desc[1] as name, a_desc[2] as type, a_desc[3] as size
from (select string_to_array(description, ' ') as a_desc from prodlist)
as foo where a_desc[2] = 'B'

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Kevin Duffy wrote:
> Noticed that string_to_array does not handle double spaces very well.
> If there are double space between the tokens, there is "" (empty string)
> in the array returned.  Not exactly what I expected.


Try regexp_replace

http://www.postgresql.org/docs/8.3/interactive/functions-string.html

Just testing the regexp_string_to_array

This SQL
select description, regexp_string_to_array(description::text , E'\\s+' )
as optdesc, securitytype     from xxxxxx where type = 'B'   order by 1

produced this error:

ERROR: function regexp_string_to_array(text, text) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You may
need to add explicit type casts.
Character: 21


Don't see the difference between the above and the example in the doc's.

kd

-----Original Message-----
From: Osvaldo Kussama [mailto:osvaldo.kussama@gmail.com]
Sent: Friday, September 05, 2008 4:47 PM
To: Kevin Duffy
Subject: Re: [SQL] variables with SELECT statement

2008/9/5, Kevin Duffy <KD@wrinvestments.com>:
> OK that is a syntax I have never seen.  But correct we are getting
> close.
>
> Noticed that string_to_array does not handle double spaces very well.
> If there are double space between the tokens, there is "" (empty
string)
>
> in the array returned.  Not exactly what I expected.
>


Try regexp_split_to_array().
http://www.postgresql.org/docs/current/interactive/functions-matching.ht
ml#FUNCTIONS-POSIX-REGEXP

SELECT regexp_split_to_array('the quick  brown    fox     jumped over
the lazy dog', E'\\s+');            regexp_split_to_array
------------------------------------------------{the,quick,brown,fox,jumped,over,the,lazy,dog}

Osvaldo

Kevin Duffy wrote:
> Just testing the regexp_string_to_array
> 
> This SQL 
> select description, regexp_string_to_array(description::text , E'\\s+' )
> as optdesc, securitytype  
>     from xxxxxx where type = 'B'   order by 1
> 
> produced this error:
> 
> ERROR: function regexp_string_to_array(text, text) does not exist
> SQL state: 42883
> Hint: No function matches the given name and argument types. You may
> need to add explicit type casts.
> Character: 21


Are you running 8.3?

Frank Bax <fbax@sympatico.ca> writes:
> Kevin Duffy wrote:
>> ERROR: function regexp_string_to_array(text, text) does not exist

> Are you running 8.3?

Also, it's regexp_split_to_array ...
        regards, tom lane

No looks like I have 8.2



-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Frank Bax
Sent: Friday, September 05, 2008 5:13 PM
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] variables with SELECT statement

Kevin Duffy wrote:
> Just testing the regexp_string_to_array
>
> This SQL
> select description, regexp_string_to_array(description::text , E'\\s+'
)
> as optdesc, securitytype
>     from xxxxxx where type = 'B'   order by 1
>
> produced this error:
>
> ERROR: function regexp_string_to_array(text, text) does not exist
> SQL state: 42883
> Hint: No function matches the given name and argument types. You may
> need to add explicit type casts.
> Character: 21


Are you running 8.3?


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Thanks Mr. Lane for catching that.

If I run
SELECT regexp_split_to_array('the quick brown fox jumped over the lazy
dog', \\s+');

Straight out of the documentation I get
ERROR: function regexp_split_to_array("unknown", "unknown") does not
exist

Let me guess I have to upgrade.


kd

-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Friday, September 05, 2008 5:27 PM
To: Frank Bax
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] variables with SELECT statement

Frank Bax <fbax@sympatico.ca> writes:
> Kevin Duffy wrote:
>> ERROR: function regexp_string_to_array(text, text) does not exist

> Are you running 8.3?

Also, it's regexp_split_to_array ...
        regards, tom lane

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

On Fri, Sep 5, 2008 at 3:28 PM, Kevin Duffy <KD@wrinvestments.com> wrote:
> No looks like I have 8.2

I can attest that all of 8.3's performance improvements as well all of
the really useful new functions like the one mentioned here make it
well worth the effort to upgrade.  I haven't been as excited about a
pgsql version since vacuum (regular) was invented.

When was 8.3 released?

But for today I could do  string_to_array(regexp_replace(description, E'\\s+', ' '), ' ') as
desc

and get what I need to survive.

Many thanks for all the replys.
Would not have made progress on this by myself.


kd

-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Friday, September 05, 2008 5:35 PM
To: Kevin Duffy
Cc: pgsql-sql@postgresql.org; Frank Bax
Subject: Re: [SQL] variables with SELECT statement

On Fri, Sep 5, 2008 at 3:28 PM, Kevin Duffy <KD@wrinvestments.com>
wrote:
> No looks like I have 8.2

I can attest that all of 8.3's performance improvements as well all of
the really useful new functions like the one mentioned here make it
well worth the effort to upgrade.  I haven't been as excited about a
pgsql version since vacuum (regular) was invented.

Kevin Duffy wrote:
> No looks like I have 8.2


This works on 8.2:

String_to_array(regexp_replace(description,E'\\s+',' ','g'),' ')