Home » SQL & PL/SQL » SQL & PL/SQL » Bulk Longest Match Query
Bulk Longest Match Query [message #187404] Sun, 13 August 2006 11:07 Go to next message
weekend79
Messages: 188
Registered: April 2005
Location: Islamabad
Senior Member

Hi

TableA
Prefix varchar2(10),
Descriptions varchar2 (50),

TableB
Numbers varchar2(30),
Area varchar2(100)

I want a query that match TableA.Prefix with TableB.Numbers in longest match fashion starting from left.

i.e.

Prefix
22
224
22559

Numbers
224498756
225597784
225515587
122559711

I need a query which match above as follows

224498756 with 224
225597784 with 22559
225515587 with 22 (not with 22559 as it not match fully with number)
122559711 with Null

I have to compare massive data so I will be more thankful if the query will be efficient.
Following is the script to create above table and load same data.

CREATE TABLE tableA
(
Prefix varchar2(10),
Descriptions varchar2 (50)
);

CREATE TABLE TableB
(
Numbers varchar2(30),
Area varchar2(100)
);

INSERT INTO tableA VALUES ('22','test');
INSERT INTO tableA VALUES ('224','test');
INSERT INTO tableA VALUES ('22559','test');


INSERT INTO tableB VALUES ('224498756','test');
INSERT INTO tableB VALUES ('225597784','test');
INSERT INTO tableB VALUES ('225515587','test');
INSERT INTO tableB VALUES ('122559711','test');



Wishes
Jawad

[Updated on: Sun, 13 August 2006 11:08]

Report message to a moderator

Re: Bulk Longest Match Query [message #187422 is a reply to message #187404] Sun, 13 August 2006 22:43 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
Does this help?
select b.numbers, d.prefix
from   tableB b, 
   (
       select b.numbers, max(a.prefix) prefix
       from tableA a, 
            tableB b,
            (select rownum r
             from   (select 1 from dual
                     connect by level <= 30) 
            ) c
       where c.r <= length(b.numbers)
       and   a.prefix = substr(b.numbers, 1, r)
       group by b.numbers
    ) d
where b.numbers  = d.numbers (+)

Re: Bulk Longest Match Query [message #187467 is a reply to message #187422] Mon, 14 August 2006 02:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Analytic solution (aren't they gorgeous! I just wish I'd ever run into a real world problem where I could use them)

  1  select distinct b.numbers
  2        ,first_value(a.prefix) over (partition by b.numbers order by length(a.prefix) desc) prefix
  3  from   tablea a, tableb b
  4* where  b.numbers like a.prefix||'%'
SQL> /

NUMBERS                       |PREFIX
------------------------------|----------
224498756                     |224
225515587                     |22
225597784                     |22559
Re: Bulk Longest Match Query [message #187507 is a reply to message #187467] Mon, 14 August 2006 04:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
NEAT! Applause, hats off!
Re: Bulk Longest Match Query [message #187515 is a reply to message #187507] Mon, 14 August 2006 05:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I wish I could work out a way of doing it without the DISTINCT though.
Re: Bulk Longest Match Query [message #187531 is a reply to message #187404] Mon, 14 August 2006 06:32 Go to previous messageGo to next message
weekend79
Messages: 188
Registered: April 2005
Location: Islamabad
Senior Member

JRowbottom / hobbes

Thank you very much
Yes they are gorgeous.

Wishes
Jawad

Re: Bulk Longest Match Query [message #187534 is a reply to message #187507] Mon, 14 August 2006 06:41 Go to previous messageGo to next message
weekend79
Messages: 188
Registered: April 2005
Location: Islamabad
Senior Member

Dear JRowbottom

Elimination of Distinct is a part of my requirement.

Plus
-- I just wish I'd ever run into a real world problem where I could use them.
Don’t worry I will run it in real world on your behalf.

Wishes
Jawad


Re: Bulk Longest Match Query [message #187565 is a reply to message #187534] Mon, 14 August 2006 08:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, I was looking to get rid of the distinct for reasons of elegance, and to remove an extra sort step in the plan, but if it is a requirement, this is the best I've got so far:

select numbers,prefix
from (select b.numbers
            ,first_value(a.prefix) over (partition by b.numbers order by length(a.prefix) desc) prefix
            ,row_number() over (partition by b.numbers order by length(a.prefix) desc) rnum
      from   tablea a, tableb b
      where  b.numbers like a.prefix||'%')
where rnum = 1;
Re: Bulk Longest Match Query [message #187584 is a reply to message #187404] Mon, 14 August 2006 09:54 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
@JRowBottom: Nice solution Cool [and I empathize with your feelings about DISTINCT!]

To eliminate it, this looks more intuitive to me, what say?
select numbers, maxprefix
from (select b.numbers
            ,a.prefix
            ,first_value(a.prefix) over (partition by b.numbers order by length(a.prefix) desc) maxprefix
      from   tablea a, tableb b
      where  b.numbers like a.prefix ||'%' )
where prefix = maxprefix;
I thought from the original requirement that tableB.numbers with no matched prefix were to be part of the result set as well. If yes then an outer join would be needed.

Re: Bulk Longest Match Query [message #187689 is a reply to message #187515] Mon, 14 August 2006 21:39 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
JRowbottom wrote on Mon, 14 August 2006 20:38

I wish I could work out a way of doing it without the DISTINCT though.


  1  select b.numbers
  2        ,min(a.prefix) keep (dense_rank last order by length(a.prefix))
  3  from   tablea a, tableb b
  4  where  b.numbers like a.prefix||'%'
  5* group by b.numbers

NUMBERS                        MIN(A.PREF
------------------------------ ----------
224498756                      224
225515587                      22
225597784                      22559


Let's not forget the ugly step-sister of the Analytic Function: the KEEP clause for aggregate functions.

Ross Leishman
Re: Bulk Longest Match Query [message #187707 is a reply to message #187404] Tue, 15 August 2006 01:38 Go to previous messageGo to next message
weekend79
Messages: 188
Registered: April 2005
Location: Islamabad
Senior Member

Dear hobbes/JRowbottom/rleishman

Thanks you very much you people give me multiple solution of my problem.

Wishes
Jawad


Re: Bulk Longest Match Query [message #187721 is a reply to message #187689] Tue, 15 August 2006 02:54 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Very nice!!!!

Previous Topic: "ORA 3114 Not connected to oracle" error after an "ORA-00054: resource busy and acqui
Next Topic: Urgent:Please help me out in Date Conversion
Goto Forum:
  


Current Time: Sat Dec 10 12:58:26 CST 2016

Total time taken to generate the page: 0.04416 seconds