Skip site navigation (1) Skip section navigation (2)

Re: String manipulation

From: "David Olbersen" <DOlbersen(at)stbernard(dot)com>
To: "pgsql-novice" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: String manipulation
Date: 2003-07-28 23:08:42
Message-ID: E7E213858379814A9AE48CA6754F5ECB0D7022@mail01.stbernard.com (view raw or flat)
Thread:
Lists: pgsql-novice
Joe,

> SELECT substring(path from position(' ' in path) + 1)
> FROM (SELECT split_part( path, ':', 2 ) AS path FROM paths) AS ss;

That's cool enough except for the sub-select which would really slow down the query on a million+ row table. My solution is just reformatting data and doesn't add much if any over head to "SELECT path FROM paths".

...but that's all speculation, am I right? I'd like to revise my thinking if I'm wrong.

--------------------------
David Olbersen 
iGuard Engineer
St. Bernard Software
11415 West Bernardo Court 
San Diego, CA 92127 
1-858-676-2277 x2152


> -----Original Message-----
> From: Joe Conway [mailto:mail(at)joeconway(dot)com]
> Sent: Monday, July 28, 2003 3:55 PM
> To: David Olbersen
> Cc: pgsql-novice
> Subject: Re: [NOVICE] String manipulation
> 
> 
> David Olbersen wrote:
> > I have an *UGLY* solution that might give you or others 
> some ideas...
> > 
> > SELECT
> >   substring(
> >     split_part( path, ':', 2 )
> >     from
> >     position( ' ' in split_part( path, ':', 2 ) ) + 1
> >   )
> > FROM paths;
> > 
> 
> That solution was pretty much what I was thinking. An alternative:
> 
> SELECT substring(path from position(' ' in path) + 1)
> FROM (SELECT split_part( path, ':', 2 ) AS path FROM paths) AS ss;
> 
> Joe
> 
> 

Responses

pgsql-novice by date

Next:From: Joe ConwayDate: 2003-07-28 23:17:30
Subject: Re: String manipulation
Previous:From: Joe ConwayDate: 2003-07-28 22:55:08
Subject: Re: String manipulation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group