Thread: Substring

Substring

From
bilal ghayyad
Date:
Hello;

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
basedon 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
firstand second? In other words, how can I can take part of the string to do query on it?
 

Regards
Bilal

     


Re: Substring

From
Raj Mathur
Date:
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.

Regards,

-- Raju
--
Raj Mathur                raju@kandalaya.org      http://kandalaya.org/      GPG: 78D4 FC67 367F 40E2 0DD5  0FEF C968
D0EFCC68 D17F 
PsyTrance & Chill: http://schizoid.in/   ||   It is the mind that moves



Re: Substring

From
Frank Bax
Date:
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).


Re: Substring

From
Raj Mathur
Date:
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
D0EFCC68 D17F
 
PsyTrance & Chill: http://schizoid.in/   ||   It is the mind that moves



Re: Substring

From
"Fernando Hevia"
Date:

> >
> > 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.