Re: Question about pattern matching

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
>