Re: Question about pattern matching

dimitris(dot)sakellarios(at)telesuite(dot)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