Re: Substring

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