From: Tom Lane <tgl@sss.pgh.pa.us>
To: gabor <gabor@nekomancer.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: text+number, find largest entry
Date: Mon, 11 Sep 2006 20:15:20 -0400
Message-ID: <8533.1158020120@sss.pgh.pa.us> (raw)
In-Reply-To: <4505F28E.6060606@nekomancer.net>
References: <4505F28E.6060606@nekomancer.net>
gabor <gabor@nekomancer.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, gabor@nekomancer.net
Subject: Re: text+number, find largest entry
In-Reply-To: <8533.1158020120@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox