Re: Question about pattern matching

Jef thanks alot for your help.
I appreciate that!
It worked fine.

Dimitris

Quoting "Hoover, Jeffrey" <jhoover(at)jcvi(dot)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(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of
> dimitris(dot)sakellarios(at)telesuite(dot)gr
> Sent: Tuesday, December 23, 2008 11:05 AM
> To: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>