Question about pattern matching

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šė:

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>:

thanks alot for your help.

Dimitris

Quoting Grzegorz Jaśkiewicz <gryzman@gmail.com>:

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