To: pgsql-sql@postgresql.org
From: Harald Fuchs <hari.fuchs@gmail.com>
Subject: Re: How to search for a part of a number
Date: Mon, 25 Oct 2010 15:13:51 +0200
Lines: 36
Message-ID: <86tykao1o0.fsf@mgm.protecting.net>
References: <4CC4D3E2.7090302@gmx.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Gnus/5.110009 (No Gnus v0.9) Emacs/22.2 (gnu/linux)
Cancel-Lock: sha1:LG3lFtoYAdHs72U38UFOXgh0s4k=

In article <4CC4D3E2.7090302@gmx.net>,
Andreas <maps.on@gmx.net> writes:

> Hi,
> I'm wondering if there was a clever way to find parts of a numeric
> string in another table.
> There is a table that holds city-codes and city-names. City-code would
> be the part of a phone number that identifies the city.
> Over here this code can have 2 - 5 digits. So the table would contain:
> 23 ; A-City
> 345 ; B-Town
> 4455 ; C-Village
> 632 ; D-Town
> ...

> I'm quite sure the numbering system is bound to be spanning a search tree.

> So there are 2 tasks:
> a)   I'd get 445598765 in and like to know the corresponding city

If the prefixes are unique:

  SELECT code, name
  FROM tbl
  WHERE '445598765' LIKE code || '%'

If not:

  SELECT code, name
  FROM tbl
  WHERE '445598765' LIKE code || '%'
  ORDER BY length(code) DESC
  LIMIT 1

If your table is so large that seqscans hurt you, use the 'prefix'
contrib package.