Re: [GENERAL] Question about pattern matching

2008-12-23 Thread Dimitris Sakellarios
Thanks depesz, It was really helpful Dimitris. -Original Message- From: dep...@depesz.com [mailto:dep...@depesz.com] Sent: Tuesday, December 23, 2008 9:18 PM To: dimitris.sakellar...@telesuite.gr Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Question about pattern matching

Re: [GENERAL] Question about pattern matching

2008-12-23 Thread hubert depesz lubaczewski
On Tue, Dec 23, 2008 at 06:05:08PM +0200, dimitris.sakellar...@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

Re: [GENERAL] Question about pattern matching

2008-12-23 Thread dimitris . sakellarios
gsql-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

Re: [GENERAL] Question about pattern matching

2008-12-23 Thread dimitris . sakellarios
thanks alot for your help. Dimitris Quoting Grzegorz Jaśkiewicz : SELECT ... from . WHERE X LIKE pattern || '%' ORDER BY length(pattern) DESC LIMIT 1; ??? basic prefix matching for telcos :P -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make change

Re: [GENERAL] Question about pattern matching

2008-12-23 Thread Grzegorz Jaśkiewicz
SELECT ... from . WHERE X LIKE pattern || '%' ORDER BY length(pattern) DESC LIMIT 1; ??? basic prefix matching for telcos :P -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Question about pattern matching

2008-12-23 Thread Julius Tuskenis
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.sakellar.

Re: [GENERAL] Question about pattern matching

2008-12-23 Thread Craig Ringer
dimitris.sakellar...@telesuite.gr wrote: > TABLENAME > > idCandidate pattern > - > 10089258068520 > 2008925806852 > 300892580685 > 40089258068 > 5008925806 > 600892580 > 70089258 > 8008925 > 900892 > 100089 > 11008 > 1200

Re: [GENERAL] Question about pattern matching

2008-12-23 Thread Scott Marlowe
I should say I'd pad them to match length with the input value. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Question about pattern matching

2008-12-23 Thread Scott Marlowe
On Tue, Dec 23, 2008 at 9:05 AM, wrote: > TABLENAME > > id Candidate pattern > - > 1 0089258068520 > 2 008925806852 > 3 00892580685 > 4 0089258068 > 5 008925806 > 6 00892580 > 7 0089258 > 8 008925 > 9 00892 > 10

[GENERAL] Question about pattern matching

2008-12-23 Thread dimitris . sakellarios
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 --- 008925700