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