Have an Oracle "connect by" SQL that looks something like :
select phone, pfx, len, (select info from codes where
pfx = x.pfx) infot
from (
select :x phone, to_number(substr( :x, 1, length(:x)-level+1 )) pfx,
length(:x)-level+1 len
from dual
connect by level <= length(:x)
order by level
) x
where rownum = 1
and (select info from codes where pfx = x.pfx) is not null
/
Where codes is essentially a two column table :
create table codes(pfx bigint,info text);
And its contents look like :
61882 Australia - Sydney
61883 Australia - Sydney
61884 Australia - Sydney
61892 Australia - Sydney
61893 Australia - Sydney
61894 Australia - Sydney
6113 Australia - Premium
6118 Australia - Premium
61 Australia - Proper
The goal being to match the longest prefix given a full phone number, e.g.
61234567890 would match "australia proper 61"
whilst
61134567890 would match "Australia premium 6113"
and
61894321010 would match "Australia - Sydney 61893"
I know the answer involves Postgres CTE, but I haven't used CTEs much
yet... let alone in complex queries such as this.
Thanking you all in advance for your kind help.
T
