> On 25 Feb 2015, at 24:50, Tim Smith <randomdev4+postgres@xxxxxxxxx> wrote: > > 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 > / > The goal being to match the longest prefix given a full phone number, e.g. > I know the answer involves Postgres CTE, but I haven't used CTEs much > yet... let alone in complex queries such as this. The CTE would look something like this, assuming that :x is some parameter from outside the query ($1 here): with recursive x(level) as ( select $1 as phone, to_number(substr($1, 1, length($1))) as pfx, length($1 ) as len, 1 as level union all select $1 as phone, to_number(substr($1, 1, length($1)-level+1 )) as pfx, length($1 ) -level+1 as len, level +1 as level from x where level <= x.len ) select * from x; Or: select $1 as phone, to_number(substr($1, 1, length($1) - pos as pfx, length($1) as len from generate_series(0, length($1)-1)(x); BTW, I didn't test any of these (I'm late already!). Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
