From: Raj Mathur <raju@linux-delhi.org>
Reply-To: raju@linux-delhi.org
Organization: Kandalaya
To: pgsql-sql@postgresql.org
Subject: Re: Substring
Date: Sat, 5 Sep 2009 09:57:40 +0530
User-Agent: KMail/1.11.4 (Linux/2.6.30-1-686; KDE/4.2.4; i686; ; )
References: <15318.85292.qm@web53905.mail.re2.yahoo.com>
	<200909050802.10371.raju@linux-delhi.org>
	<4AA1E4C9.1000108@sympatico.ca>
In-Reply-To: <4AA1E4C9.1000108@sympatico.ca>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200909050957.40591.raju@linux-delhi.org>

On Saturday 05 Sep 2009, Frank Bax wrote:
> Raj Mathur wrote:
> > On Saturday 05 Sep 2009, bilal ghayyad wrote:
> >> I have an sql script function that take one text parameter "funct
> >> (text)", what I need to do is the following:
> >>
> >> If the parameter name is string and its value was for example
> >> "abcd" then I need to do a query based on ab and then based on the
> >> abc, how?
> >>
> >> Example:
> >>
> >> SELECT * from voipdb where prefix like string
> >>
> >> But I need the string to be ab and then to be abc? How I can
> >> assign the string to the first character and then to the first and
> >> second? In other words, how can I can take part of the string to
> >> do query on it?
> >
> > From your example the following brute-force method should work (not
> > tested):
> >
> > select * from voipdb where prefix like substring(string from 1 for
> > 2) || '%' or prefix like substring(string from 1 for 3) || '%';
> >
> > However, I don't understand why you'd want to search for both 'ab'
> > and 'abc' in the same query, since the first condition is a
> > superset of the second one.
>
> 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
-- 
Raj Mathur                raju@kandalaya.org      http://kandalaya.org/
       GPG: 78D4 FC67 367F 40E2 0DD5  0FEF C968 D0EF CC68 D17F
PsyTrance & Chill: http://schizoid.in/   ||   It is the mind that moves