Обсуждение: Question about pattern matching
TABLENAME id Candidate pattern ------------------------- 1 0089258068520 2 008925806852 3 00892580685 4 0089258068 5 008925806 6 00892580 7 0089258 8 008925 9 00892 10 0089 11 008 12 00 13 0 PATTERN ------- 0089257000000 QUESTION -------- Pls let me know which is the best way to match the following pattern against this table and Receive id = 8 that is the longest matching pattern from left to right. BR, Sakellarios Dimitris.
On Tue, Dec 23, 2008 at 9:05 AM, <dimitris.sakellarios@telesuite.gr> wrote: > TABLENAME > > id Candidate pattern > ------------------------- > 1 0089258068520 > 2 008925806852 > 3 00892580685 > 4 0089258068 > 5 008925806 > 6 00892580 > 7 0089258 > 8 008925 > 9 00892 > 10 0089 > 11 008 > 12 00 > 13 0 > > > PATTERN > ------- > 0089257000000 > > QUESTION > -------- > Pls let me know which is the best way to match the > following pattern against this table and Receive id = 8 > that is the longest matching pattern from left to right. Without getting out the pgsql manual to write the code, I'd basically append enough 0s to each candidate to pad them out to the same length, all ending in one or more 0s, except the longest, which wouldn't need padding. Then I'd see which one matched.
I should say I'd pad them to match length with the input value.
dimitris.sakellarios@telesuite.gr wrote: > TABLENAME > > id Candidate pattern > ------------------------- > 1 0089258068520 > 2 008925806852 > 3 00892580685 > 4 0089258068 > 5 008925806 > 6 00892580 > 7 0089258 > 8 008925 > 9 00892 > 10 0089 > 11 008 > 12 00 > 13 0 > > > PATTERN > ------- > 0089257000000 > > QUESTION > -------- > Pls let me know which is the best way to match the > following pattern against this table and Receive id = 8 > that is the longest matching pattern from left to right. The simplest way would be to select the id of the record with max(length(pattern)), using a WHERE clause to filter for only matching patterns by comparing the substring of both pattern and teststr of length min(length(pattern),length(teststr)) for equality. -- Craig Ringer
Hi, Dimitris I think simple SELECT TABLENAME.id FROM TABLENAME WHERE prm_patern ilike TABLENAME.candidate_pattern||'%' ORDER BY |char_length(|TABLENAME.candidate_pattern) desc LIMIT 1 should do the trick. (Provided TABLENAME is not very large of course) Julius Tuskenis dimitris.sakellarios@telesuite.gr rašė: > TABLENAME > > id Candidate pattern > ------------------------- > 1 0089258068520 > 2 008925806852 > 3 00892580685 > 4 0089258068 > 5 008925806 > 6 00892580 > 7 0089258 > 8 008925 > 9 00892 > 10 0089 > 11 008 > 12 00 > 13 0 > > > PATTERN > ------- > 0089257000000 > > QUESTION > -------- > Pls let me know which is the best way to match the > following pattern against this table and Receive id = 8 > that is the longest matching pattern from left to right. > > BR, > Sakellarios Dimitris. > >
SELECT ... from ..... WHERE X LIKE pattern || '%' ORDER BY length(pattern) DESC LIMIT 1; ??? basic prefix matching for telcos :P -- GJ
Jef thanks alot for your help. I appreciate that! It worked fine. Dimitris Quoting "Hoover, Jeffrey" <jhoover@jcvi.org>: > > cameradb_dev=# select id, Candidate_pattern > from all_patterns > where :pattern like Candidate_pattern||'%' > and candidate_pattern between substring(:pattern from 1 for 1) and > :pattern > order by length(Candidate_pattern) desc > limit 1; > > id | candidate_pattern > ----+------------------- > 8 | 008925 > (1 row) > > note 1: bind (or substitute) your value for :pattern > > note 2: "and candidate_pattern between..." only helps > if candidate_pattern is indexed, if there aren't > many rows it is not necessary > > > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of > dimitris.sakellarios@telesuite.gr > Sent: Tuesday, December 23, 2008 11:05 AM > To: pgsql-general@postgresql.org > Subject: [GENERAL] Question about pattern matching > > TABLENAME > > id Candidate pattern > ------------------------- > 1 0089258068520 > 2 008925806852 > 3 00892580685 > 4 0089258068 > 5 008925806 > 6 00892580 > 7 0089258 > 8 008925 > 9 00892 > 10 0089 > 11 008 > 12 00 > 13 0 > > > PATTERN > ------- > 0089257000000 > > QUESTION > -------- > Pls let me know which is the best way to match the > following pattern against this table and Receive id = 8 > that is the longest matching pattern from left to right. > > BR, > Sakellarios Dimitris. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
thanks alot for your help. Dimitris Quoting Grzegorz Jaśkiewicz <gryzman@gmail.com>: > SELECT ... from ..... WHERE X LIKE pattern || '%' ORDER BY > length(pattern) DESC LIMIT 1; > > ??? > > basic prefix matching for telcos :P > > -- > GJ >
On Tue, Dec 23, 2008 at 06:05:08PM +0200, dimitris.sakellarios@telesuite.gr wrote: > QUESTION > -------- > Pls let me know which is the best way to match the > following pattern against this table and Receive id = 8 > that is the longest matching pattern from left to right. check this: http://www.depesz.com/index.php/2008/03/04/searching-for-longest-prefix/ read also the comments additionally, check this url: http://pgfoundry.org/projects/prefix Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
Thanks depesz, It was really helpful Dimitris. -----Original Message----- From: depesz@depesz.com [mailto:depesz@depesz.com] Sent: Tuesday, December 23, 2008 9:18 PM To: dimitris.sakellarios@telesuite.gr Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Question about pattern matching On Tue, Dec 23, 2008 at 06:05:08PM +0200, dimitris.sakellarios@telesuite.gr wrote: > QUESTION > -------- > Pls let me know which is the best way to match the > following pattern against this table and Receive id = 8 > that is the longest matching pattern from left to right. check this: http://www.depesz.com/index.php/2008/03/04/searching-for-longest-prefix/ read also the comments additionally, check this url: http://pgfoundry.org/projects/prefix Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007 __________ NOD32 3693 (20081215) Information __________ This message was checked by NOD32 antivirus system. http://www.eset.com