Jef thanks alot for your help.
I appreciate that!
It worked fine.
Dimitris
Quoting "Hoover, Jeffrey" <jhoo...@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-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of
dimitris.sakellar...@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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
