On Feb 24, 2015, at 3:50 PM, Tim Smith <randomdev4+postgres@xxxxxxxxx> wrote: > > > 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. There's probably a CTE approach for it, but you might want to look at https://github.com/dimitri/prefix too - it's an extension that's designed specifically for longest prefix matching, and that uses gist indexes to do it efficiently. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
