Re: Finding a pattern with SQL or SQLPlus

In article <7vddul$oli$1_at_news4.svr.pol.co.uk>,   "-=< a q u a b u b b l e >=-"
<aquabubble_at_Remove.No.Parma.Ham.Remove.geocities.com> wrote:
> Ken Hill <hillke_at_umkc.edu> wrote in message
> news:7vdah3$rev$1_at_ns3.umkc.edu...
> > Oct 29, 1999
> >
> > I need some assistance and hoepfully I'm not the
first
> person
> > to encounter such a problem. I have an Oracle 7 database and I
need to
> find
> > a pattern in some records. Assume the records are in the table as
> follows:
> >
> > 1 T
> > 2 T
> > 3 K
> > 4 K
> > 5 K
> > 6 T
> > 7 T
> > 8 T
> > 9 K
> > 10 K
> > 11 T
> > 12 T
> > 13 T
> > 14 K
> > 15 T
> > 16 T
> >
> > I need to determing if this sequence of records contains a
pattern of
> > this type 'TKTK'. Now here is the the interesting thing, this set
of
> > records does contain this pattern. These records are composed of 2
T's, 3
> > K's, 3 T's, 2 K's, 3 T's, 1 K, 2 T's. Hence this sequence of
records does
> > contain a 'TKTK' pattern if you ignore repeated instances of a
character.
> > In fact it contains the pattern twice once in records 1-10 and
again in
> > records 6-14. In fact you could argue that the pattern exists many
more
> > times if you consider the set of records 2-10, records 1-9, records
2-9,
> > etc. But I am only interested in the largest sequence records.
Also note,
> > the pattern 'TKTK' does not exist in records 1-8, records 3-13, or
records
> > 11-16 or any other combination.
> >
> > Knowing that this pattern exists is minimally sufficient, I
would also
> > like to know where the pattern begins and ends.
> >
> >
> > One other question, how can I take the above sequence of
records and
> > turn it into a string (i.e. TTKKKTTTKKTTTKTT)?
>
> In PL/SQL: A cursor to fetch each row, and then build up a string
appending
> the new result onto the end. If you use a long for your string, then
you can
> build it up to a maximum of 32k.

>
> Also, you could code some logic into this program that would do as
you want
> in the first part of your program. Would be best to do this row by
row I
> think... unless anyone has any better ideas.
>

I like ORACLE, but its pattern matching tools are weak. I'm a strong believer in the "right tool for the job. The approach I would use is to have PERL do the pattern matching.

Assuming your records are ordered, select them out to a file. Then have a perl script which produces the results you need. (My perl is rusty tonight, been doing C code recently) your pattern is something like:

       T+K+T+K+

Finding the existance of the pattern is easy, finding its location takes a little more work. But it's not very difficult.

--
Ed Prochak
Magic Interface, Ltd.
ORACLE Development, conversions, training and support 440-498-3700 magic_at_interfacefamily.com

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Oct 29 1999 - 23:42:36 CDT