Thread: Substring
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
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
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).
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
> > > > 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.