pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: tjk@tksoft.com <tjk@tksoft.com>
To: tom@eurobase.net
Cc: kaiq@realtyideas.com
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] A search SQL query
Date: Thu, 2 Dec 1999 21:56:36 -0800 (PST)
Message-ID: <199912030556.VAA22306@uno.tksoft.com> (raw)
In-Reply-To: <003701bf3d0d$a4becc60$157999cf@eurobase.net>

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




view thread (6+ messages)

Message-ID: <199912030556.VAA22306@uno.tksoft.com>
Permalink:  ../199912030556.VAA22306@uno.tksoft.com/
Also on:    postgresql.org/message-id/199912030556.VAA22306@uno.tksoft.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tjk@tksoft.com, tom@eurobase.net, kaiq@realtyideas.com
  Subject: Re: [SQL] A search SQL query
  In-Reply-To: <199912030556.VAA22306@uno.tksoft.com>
  References: <003701bf3d0d$a4becc60$157999cf@eurobase.net> <199912030556.VAA22306@uno.tksoft.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox