Re: text+number, find largest entry

gabor <gabor(at)nekomancer(dot)net> writes:
> i have a table, where there is a varchar(500) column,
> which contains data that is strangely formatted:
> it starts with letters, and ends with a number.
> for example:

> xyz001
> xyz002
> xyz044
> xyz1243
> abc01
> abc993
> abc2342

> now, for a given text-prefix (for example "xyz"), i need to
> find the record with the largest "numeric component".

I'd try using a couple of regexp_replace() calls to pull out the text
prefix and number separately. Then you could group by the one and order
by the other in your favorite variant of the DISTINCT ON pattern.
(See weather-report example in the SELECT reference page if you have
no idea what I'm talking about.)

> this lookup does not have to be especially fast.

Good ;-) ... otherwise changing your schema would definitely be indicated.

regards, tom lane