Re: Searching Text in words

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Thu, 9 Oct 2008 04:07:12 -0700 (PDT)
Message-ID: <40cbf334-02d9-460e-b67c-6702fdfd58b2@d70g2000hsc.googlegroups.com>

On Oct 8, 8:13 pm, artme..._at_yahoo.com wrote:
> On Oct 8, 8:50 am, yossarian <yossaria..._at_operamail.com> wrote:
>
> > artme..._at_yahoo.com wrote:
> > > I was looking at Oracle Text, but I think that is a bit much and
> > > complex for this application.  I can parse each name on spaces and
> > > such and try it that way, but I was wondering if Oracle had a better
> > > way of doing it.
>
> > > We are on 10g R2.
>
> > Did you check out if REGEXP_LIKE is suitable for your needs?
>
> > Kind regards,
> > Y.
>
> REGEXP_LIKE does not seem to have anything that would search at the
> beginning of every word, including the first word.
>
> I was looking at owa_pattern.match, that does the job, but
> unfortunately it does not fit the scope of what we need.  We need to
> run this expression to get the data, then open up a cursor and have
> the PHP application select from the recordset.
>
> That means I need to run a SELECT of some sorts, and while
> owa_pattern.match does the job, I do not think I can use it in a
> select statement.  Though I am still looking.......

It does, you just need to get the regexp right, and with a bit of shaman dancing, voodoo and black magic applied you can build a regexp for anything. Your particular case is pretty easy and doesn't require any occultism at all: '^{search}|\s{search}', where {search} is your search string (without curly braces.) For 'B' the expression will be '^B|\sB', for 'Bu' it will be '^Bu|\sBu', etc.:

SQL> with q as
  2 (

  3     select 'International Business Machines' as text from dual
  4     union
  5     select 'Best Buy' from dual
  6     union
  7     select 'Lehman Brothers' from dual
  8     union
  9     select 'Merrill Lynch' from dual

 10 )
 11 select text from q where regexp_like(text,'^B|\sB')  12 /

TEXT

---

Best Buy
International Business Machines
Lehman Brothers

SQL> with q as
  2 (

  3     select 'International Business Machines' as text from dual
  4     union
  5     select 'Best Buy' from dual
  6     union
  7     select 'Lehman Brothers' from dual
  8     union
  9     select 'Merrill Lynch' from dual

 10 )
 11 select text from q where regexp_like(text,'^Bu|\sBu')  12 /

TEXT

---

Best Buy
International Business Machines

Hth,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Thu Oct 09 2008 - 06:07:12 CDT