pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

text+number, find largest entry
2+ messages / 2 participants
[nested] [flat]

* text+number, find largest entry
@ 2006-09-11 23:34  gabor <gabor@nekomancer.net>
  0 siblings, 1 reply; 2+ messages in thread

From: gabor @ 2006-09-11 23:34 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: text+number, find largest entry
@ 2006-09-12 00:15  Tom Lane <tgl@sss.pgh.pa.us>
  parent: gabor <gabor@nekomancer.net>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2006-09-12 00:15 UTC (permalink / raw)
  To: gabor <gabor@nekomancer.net>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2006-09-12 00:15 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-11 23:34 text+number, find largest entry gabor <gabor@nekomancer.net>
2006-09-12 00:15 ` Tom Lane <tgl@sss.pgh.pa.us>

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