Thread: variables with SELECT statement
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Hello All:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">I have a simple issue.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Within my table there is a field DESCRIPTION that I would like to parse and split out into other fields.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Within DESCRIPTION there are spaces that separate the data items. String_to_array(description, ‘ ‘)does the job very well.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">I need something like this to work.</span></font><p class="MsoNormal"><font face="Arial" size="2"><spanstyle="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> select string_to_array(description, ' ') as a_desc, </span></font><p class="MsoNormal"><font face="Arial"size="2"><span style="font-size:10.0pt; font-family:Arial"> a_desc[0] as name , a_desc[1] as type, a_desc[2] as size, from prodlist where type ='B'</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Need to parse the DESCRIPTION and then reference the pieces.</span></font><p class="MsoNormal"><font face="Arial"size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Your kind assistance is requested.</span></font><p class="MsoNormal"><font face="Arial" size="2"><spanstyle="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Thanks</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Kevin Duffy</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">WR Capital Management</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">40Signal Rd</span></font><p class="MsoNormal"><font face="Arial" size="2"><spanstyle="font-size:10.0pt;font-family:Arial">Stamford</span></font><font face="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">,CT</span></font><font face="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial"></span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">203-504-6221</span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size: 12.0pt"> </span></font></div>
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'),' ')