From: Fernando Hevia <fhevia@ip-tel.com.ar>
To: raju@linux-delhi.org
To: pgsql-sql@postgresql.org
Subject: Re: Substring
Date: Mon, 7 Sep 2009 15:36:58 -0300
Message-ID: <9CC5D98DF7ED4FE7AFC70F08F75021E3@iptel.com.ar> (raw)
In-Reply-To: <200909050957.40591.raju@linux-delhi.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>
> >
> > 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: fhevia@ip-tel.com.ar, raju@linux-delhi.org
Subject: Re: Substring
In-Reply-To: <9CC5D98DF7ED4FE7AFC70F08F75021E3@iptel.com.ar>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox