Re: [SQL] A search SQL query

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