## Re: Question about pattern matching

From: Craig Ringer dimitris(dot)sakellarios(at)telesuite(dot)gr pgsql-general(at)postgresql(dot)org Re: Question about pattern matching 2008-12-23 17:16:02 49511CD2.20208@postnewspapers.com.au (view raw, whole thread or download thread mbox) 2008-12-23 16:05:08 from dimitris(dot)sakellarios(at)telesuite(dot)gr  2008-12-23 17:12:59 from "Scott Marlowe"   2008-12-23 17:13:29 from "Scott Marlowe"  2008-12-23 17:16:02 from Craig Ringer  2008-12-23 17:37:02 from Julius Tuskenis  2008-12-23 18:06:34 from Grzegorz JaĆkiewicz   2008-12-23 18:12:19 from dimitris(dot)sakellarios(at)telesuite(dot)gr  2008-12-23 18:11:24 from dimitris(dot)sakellarios(at)telesuite(dot)gr  2008-12-23 19:17:33 from hubert depesz lubaczewski   2008-12-23 19:30:41 from "Dimitris Sakellarios" pgsql-general
```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

```

### pgsql-general by date

 Next: From: Julius Tuskenis Date: 2008-12-23 17:37:02 Subject: Re: Question about pattern matching Previous: From: Scott Marlowe Date: 2008-12-23 17:13:29 Subject: Re: Question about pattern matching