From: "Fernando Hevia" <fhevia@ip-tel.com.ar>
To: <raju@linux-delhi.org>,
	<pgsql-sql@postgresql.org>
References: <15318.85292.qm@web53905.mail.re2.yahoo.com>
	<200909050802.10371.raju@linux-delhi.org>
	<4AA1E4C9.1000108@sympatico.ca>
	<200909050957.40591.raju@linux-delhi.org>
Subject: Re: Substring
Date: Mon, 7 Sep 2009 15:36:58 -0300
Organization: IP-TEL
Message-ID: <9CC5D98DF7ED4FE7AFC70F08F75021E3@iptel.com.ar>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
In-Reply-To: <200909050957.40591.raju@linux-delhi.org>
Thread-Index: Acot4VKXpYCRr8zoRbiOYsb1iWlpcwCB648Q

 
> >
> > Given that tablename is "voipdb"; I wonder if OP really 
> wants to write 
> > a query that finds the row where argument to function 
> matches the most 
> > number of leading characters in "prefix".
> >
> > If voipdb table contains:  ab, abc, def, defg; then calling 
> function 
> > with "abc" or "abcd" returns "abc" and calling function with "defh"
> > returns "def".
> >
> > If this is the real problem to be solved; then brute force is one 
> > solution; but I'm left wondering if a single query might return 
> > desired result (a single row).
> 
> Something like this may help in that case (note, we're 
> completely in the realm of creating imaginary problems and 
> solving them now :)
> 
> select * from voipdb where prefix <= string order by prefix 
> desc limit 1;
> 
> Regards,
> 
> -- Raju

Hum, I wonder if some kind of best-matching query is what you are looking
for:

    SELECT *
    FROM voipdb
    WHERE prefix IN (
       SELECT substr(string, 1, i)
       FROM generate_series(1, length(string)) i
    );


Cheers,
Fernando.