Re: Question about pattern matching

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