pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
How to search for a part of a number 2+ messages / 2 participants [nested] [flat]
* How to search for a part of a number @ 2010-10-25 00:48 Andreas <maps.on@gmx.net> 2010-10-25 13:13 ` Re: How to search for a part of a number Harald Fuchs <hari.fuchs@gmail.com> 0 siblings, 1 reply; 2+ messages in thread From: Andreas @ 2010-10-25 00:48 UTC (permalink / raw) To: pgsql-sql 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? ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: How to search for a part of a number 2010-10-25 00:48 How to search for a part of a number Andreas <maps.on@gmx.net> @ 2010-10-25 13:13 ` Harald Fuchs <hari.fuchs@gmail.com> 0 siblings, 0 replies; 2+ messages in thread From: Harald Fuchs @ 2010-10-25 13:13 UTC (permalink / raw) To: pgsql-sql 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. ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-10-25 13:13 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2010-10-25 00:48 How to search for a part of a number Andreas <maps.on@gmx.net> 2010-10-25 13:13 ` Harald Fuchs <hari.fuchs@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox