Thread: A search SQL query
Hi, I am using a cursor search using sql queries to get information from a database that has 5 fields. The search is done on one of the fields which are "electronic components parts". My users usually type in the all part number they are looking for in the form. Here is the situation I have, lets say that a user is looking for part 'LM741CN-SGS', I have the follwoing queries: - select * from inventory where partno like 'LM741CN-SGS' (exact search) - select * from inventory where partno like 'LM741CN-SGS%' (partial search) - select * from inventory where partno like '%LM741CN-SGS%' (extended search) Now I need to create a query that when the user looks for 'LM741CNSGS', the result comes back with all rows with 'LM741CN-SGS' and and all rows with 'LM741CNSGS'. What would the suggested sql query to provide that type of result? I would appreciate any feedback. Thank you, Tom Techoueyres tom@eurobase.net
the bottom line: you may need to use regular expression. However, I'm not sure it's performance. Also, it is not portable. So, if not really required, I do not use it. a simple minded solution: just use "or": select * from mytable where note = 'LM741CNSGS' or note = 'LM741CN-SGS'; the table name and filed name is not the same as yours. please give us your "create table" so that we can cut/paste. On Thu, 2 Dec 1999, Tom Techoueyres wrote: > Hi, > > I am using a cursor search using sql queries to get information from a > database that has 5 fields. The search is done on one of the fields which > are "electronic components parts". > My users usually type in the all part number they are looking for in the > form. > Here is the situation I have, lets say that a user is looking for part > 'LM741CN-SGS', I have the follwoing queries: > - select * from inventory where partno like 'LM741CN-SGS' (exact search) > - select * from inventory where partno like 'LM741CN-SGS%' (partial search) > - select * from inventory where partno like '%LM741CN-SGS%' (extended > search) > > Now I need to create a query that when the user looks for 'LM741CNSGS', the > result comes back with all rows with 'LM741CN-SGS' and and all rows with > 'LM741CNSGS'. > What would the suggested sql query to provide that type of result? > > I would appreciate any feedback. > > Thank you, > > Tom Techoueyres > tom@eurobase.net > > > ************ >
Remember that the user will only type 'LM741CNSGS' in the form, and through the query, the results would bring back all 'LM741CNSGS' and all 'LM741CN-SGS' items. What actually i would like the query to do is when searching for 'LM741CNSGS' in the database, it will bring back all 'LM741CNSGS' parts that have a '-' in the part. I hope I explained it better! :) thanks, Tom Techoueyres tom@eurobase.net > -----Original Message----- > From: kaiq@realtyideas.com [mailto:kaiq@realtyideas.com] > Sent: Thursday, December 02, 1999 5:27 PM > To: Tom Techoueyres > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] A search SQL query > > > the bottom line: you may need to use regular expression. However, I'm not > sure it's performance. Also, it is not portable. So, if not really > required, I do not use it. > > a simple minded solution: just use "or": > select * from mytable where note = 'LM741CNSGS' or note = 'LM741CN-SGS'; > > the table name and filed name is not the same as yours. please give us > your "create table" so that we can cut/paste. > > > On Thu, 2 Dec 1999, Tom Techoueyres wrote: > > > Hi, > > > > I am using a cursor search using sql queries to get information from a > > database that has 5 fields. The search is done on one of the > fields which > > are "electronic components parts". > > My users usually type in the all part number they are looking for in the > > form. > > Here is the situation I have, lets say that a user is looking for part > > 'LM741CN-SGS', I have the follwoing queries: > > - select * from inventory where partno like 'LM741CN-SGS' > (exact search) > > - select * from inventory where partno like 'LM741CN-SGS%' > (partial search) > > - select * from inventory where partno like '%LM741CN-SGS%' (extended > > search) > > > > Now I need to create a query that when the user looks for > 'LM741CNSGS', the > > result comes back with all rows with 'LM741CN-SGS' and and all rows with > > 'LM741CNSGS'. > > What would the suggested sql query to provide that type of result? > > > > I would appreciate any feedback. > > > > Thank you, > > > > Tom Techoueyres > > tom@eurobase.net > > > > > > ************ > > >
seems we'll fall below the bottom line: even regular expression can not work here. Since "-" can be anywhere, no easy way out. one way to do it is to purge your db, eliminate the "-"s in your db. then, eliminate the "-"s before make query in you program. On Thu, 2 Dec 1999, Tom Techoueyres wrote: > Remember that the user will only type 'LM741CNSGS' in the form, and through > the query, the results would bring back all 'LM741CNSGS' and all > 'LM741CN-SGS' items. > What actually i would like the query to do is when searching for > 'LM741CNSGS' in the database, it will bring back all 'LM741CNSGS' parts that > have a '-' in the part. > > I hope I explained it better! :) > > thanks, > > Tom Techoueyres > tom@eurobase.net > > > > -----Original Message----- > > From: kaiq@realtyideas.com [mailto:kaiq@realtyideas.com] > > Sent: Thursday, December 02, 1999 5:27 PM > > To: Tom Techoueyres > > Cc: pgsql-sql@postgresql.org > > Subject: Re: [SQL] A search SQL query > > > > > > the bottom line: you may need to use regular expression. However, I'm not > > sure it's performance. Also, it is not portable. So, if not really > > required, I do not use it. > > > > a simple minded solution: just use "or": > > select * from mytable where note = 'LM741CNSGS' or note = 'LM741CN-SGS'; > > > > the table name and filed name is not the same as yours. please give us > > your "create table" so that we can cut/paste. > > > > > > On Thu, 2 Dec 1999, Tom Techoueyres wrote: > > > > > Hi, > > > > > > I am using a cursor search using sql queries to get information from a > > > database that has 5 fields. The search is done on one of the > > fields which > > > are "electronic components parts". > > > My users usually type in the all part number they are looking for in the > > > form. > > > Here is the situation I have, lets say that a user is looking for part > > > 'LM741CN-SGS', I have the follwoing queries: > > > - select * from inventory where partno like 'LM741CN-SGS' > > (exact search) > > > - select * from inventory where partno like 'LM741CN-SGS%' > > (partial search) > > > - select * from inventory where partno like '%LM741CN-SGS%' (extended > > > search) > > > > > > Now I need to create a query that when the user looks for > > 'LM741CNSGS', the > > > result comes back with all rows with 'LM741CN-SGS' and and all rows with > > > 'LM741CNSGS'. > > > What would the suggested sql query to provide that type of result? > > > > > > I would appreciate any feedback. > > > > > > Thank you, > > > > > > Tom Techoueyres > > > tom@eurobase.net > > > > > > > > > ************ > > > > > >
<kaiq@realtyideas.com> writes: > seems we'll fall below the bottom line: even regular expression can not > work here. Since "-" can be anywhere, no easy way out. Sure you can do it with a regular expression ... it's ugly but you can do it: where partno ~ 'L-?M-?7-?4-?1-?C-?N-?S-?G-?S' or possibly where partno ~ 'L-*M-*7-*4-*1-*C-*N-*S-*G-*S' if you need to allow multiple consecutive dashes. '-?' means 0 or 1 dash, '-*' means 0 or more dashes... regards, tom lane
Tom, You need to write a function which deletes the '-' character from the strings in the table, and does a search on those. The easiest would be to write the function in C, with the body being something like: char * trimem (char * inputstr) { char * str; char * tmp; if (! inputstr) return NULL; str = strdup(inputstr); if (! str) return NULL; for (;;) { tmp = index(str, '-'); if (! tmp) return str; *(tmp++) = '\0'; strcat(str, tmp); } return str; } I tested this once, to make sure it at least pretends to do the job. Of course yoou need to copy and paste it into one of the samples in the samples directory. The samples directory contains sample functions which are easy to modify and install. Just compile the function as a shared object, write the command to create the function as a C function, install the object, restart postgres, run the create function command, and start using it. If you called your function "trimem," you would do a search with "select * from inventory where trimem(partno) = 'LM741CNSGS'" (or something like that.) The other alternative is to play with the string functions. You can find them in the "user" documentation, under string functions. get an index first and then a substring, and then concatenate the two in the result. Seems like this would be a slow approach, but maybe the people who know the internals of how a SQL functions is implemented know different. Good luck, Troy > > Remember that the user will only type 'LM741CNSGS' in the form, and through > the query, the results would bring back all 'LM741CNSGS' and all > 'LM741CN-SGS' items. > What actually i would like the query to do is when searching for > 'LM741CNSGS' in the database, it will bring back all 'LM741CNSGS' parts that > have a '-' in the part. > > I hope I explained it better! :) > > thanks, > > Tom Techoueyres > tom@eurobase.net > > > > -----Original Message----- > > From: kaiq@realtyideas.com [mailto:kaiq@realtyideas.com] > > Sent: Thursday, December 02, 1999 5:27 PM > > To: Tom Techoueyres > > Cc: pgsql-sql@postgresql.org > > Subject: Re: [SQL] A search SQL query > > > > > > the bottom line: you may need to use regular expression. However, I'm not > > sure it's performance. Also, it is not portable. So, if not really > > required, I do not use it. > > > > a simple minded solution: just use "or": > > select * from mytable where note = 'LM741CNSGS' or note = 'LM741CN-SGS'; > > > > the table name and filed name is not the same as yours. please give us > > your "create table" so that we can cut/paste. > > > > > > On Thu, 2 Dec 1999, Tom Techoueyres wrote: > > > > > Hi, > > > > > > I am using a cursor search using sql queries to get information from a > > > database that has 5 fields. The search is done on one of the > > fields which > > > are "electronic components parts". > > > My users usually type in the all part number they are looking for in the > > > form. > > > Here is the situation I have, lets say that a user is looking for part > > > 'LM741CN-SGS', I have the follwoing queries: > > > - select * from inventory where partno like 'LM741CN-SGS' > > (exact search) > > > - select * from inventory where partno like 'LM741CN-SGS%' > > (partial search) > > > - select * from inventory where partno like '%LM741CN-SGS%' (extended > > > search) > > > > > > Now I need to create a query that when the user looks for > > 'LM741CNSGS', the > > > result comes back with all rows with 'LM741CN-SGS' and and all rows with > > > 'LM741CNSGS'. > > > What would the suggested sql query to provide that type of result? > > > > > > I would appreciate any feedback. > > > > > > Thank you, > > > > > > Tom Techoueyres > > > tom@eurobase.net > > > > > > > > > ************ > > > > > > > > ************ > >