Thread: How to search for a part of a number
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?
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.