Received: from om.kick.com (IDENT:root@adsl-63-202-92-187.dsl.snfc21.pacbell.net [63.202.92.187]) by hub.org (8.9.3/8.9.3) with ESMTP id SAA83673 for ; Thu, 18 May 2000 18:03:15 -0400 (EDT) (envelope-from sszabo@kick.com) Received: from planetblargh (adsl-63-202-92-189.dsl.snfc21.pacbell.net [63.202.92.189]) by om.kick.com (8.9.3/8.9.3) with SMTP id PAA14586; Thu, 18 May 2000 15:02:41 -0700 Message-ID: <024001bfc114$b25c18b0$0c64010a@kick.com> From: "Stephan Szabo" To: "Ross J. Reedstrom" Cc: References: <3924405E.15014E6B@wsicorp.com> <200005181938.QAA04835@ant.utdt> <20000518153909.B2549@rice.edu> Subject: Re: SQL command speed Date: Thu, 18 May 2000 15:02:02 -0700 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2919.6700 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700 X-Archive-Number: 200005/73 I didn't see a function that would do what you wanted to do, but I guess you could do the trimming in a plpgsql function though... (Since I don't have a 7.0 system to test with, these were only tested on an old 6.5.1 database)... create function trimthe(text) returns text as ' begin if (substr($1, 1, 4) = \'The \' then return substr($1, 5); end if; -- add other prefix checks here... return $1; end; ' language 'plpgsql'; and then select ordering by that... I guess a more general function could be: create function ltrimstr(text, text) returns text as ' begin if position($2 in $1) = 1 then return substr($1, char_length($2)+1); end if; return $1; end; ' language 'plpgsql'; ----- Original Message ----- From: "Ross J. Reedstrom" To: Sent: Thursday, May 18, 2000 1:39 PM Subject: Re: [SQL] SQL command speed > Hey crew: > I've got a relatively simple SQL problem. In a db backed web site > we're building, I'd like to fill a dropdown box with the contents of a > validation table, in this case research institutions. I want to sort them > alphabetically, but using "library rules": i.e. skip inital articles, > since we've a few 'The University of Foo" and "The Johns Hopkins > University", for example. > > I thought I had it with this SQL: > > SELECT InstName from Institutions ORDER BY ltrim (InstName, 'The'); > > Looked good, until I found 'Texas A&M University' sorting below York. > > Seems ltrim() removes inital charaters from the set of charaters, not > inital strings, so I was sorting on 'xas A&M University' > > Anyone have some magic solution for this?