In article <4cc4d3e2.7090...@gmx.net>, Andreas <maps...@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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql