Message-ID: <4CC4D3E2.7090302@gmx.net>
Date: Mon, 25 Oct 2010 02:48:34 +0200
From: Andreas <maps.on@gmx.net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de;
	rv:1.9.2.11) Gecko/20101013 Thunderbird/3.1.5
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: How to search for a part of a number
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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
b)   I'd like to get the number nicely formatted :  4 4 55987-65  -->  
4455 / 98765

Obviously one could do it on the client side.
As I dont know how many digits in the input are relevant, I need to try 
the shortest code first.
1)  search for 44
2)  add 5 and search for 445
3)  add the 2nd. 5 and search for 4455
BINGO

Is there a way to do it within the db?