From: gabor <gabor@nekomancer.net>
To: pgsql-sql@postgresql.org
Subject: text+number, find largest entry
Date: Tue, 12 Sep 2006 01:34:38 +0200
Message-ID: <4505F28E.6060606@nekomancer.net> (raw)
hi,
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
and so on.
now, for a given text-prefix (for example "xyz"), i need to
find the record with the largest "numeric component".
so for example, for the text-prefix "xyz", the corresponding entry would
be "xyz1243".
this lookup does not have to be especially fast.
i realize that i could add some additional columns to this table,
and store the text-part and the numeric-part separately,
but first i would prefer a non-alter-table solution :)
currently my only idea is to find the longest entry, check how many of
them are, and then find the ones whose numeric part starts with "9"
etc... ugly, but should work.
are there any better ways to do it?
thanks,
gabor
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: gabor@nekomancer.net
Subject: Re: text+number, find largest entry
In-Reply-To: <4505F28E.6060606@nekomancer.net>
* 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