max of a varchar field

#1

Hello all,
i have a problem with the max value of a varchar field

id docno description
=================
1 2 test1
2 F10 test2

select max(docno::text) as "docno" from table

I woul like that it returns me F10, but it doesn't... how can i tell him to
give me the max value without consider the char part?

thanx all
Dade

#2

Dade <cucca77NOSPAM (AT) nospamgmail (DOT) com> wrote:

Quote:

i have a problem with the max value of a varchar field

id docno description
=================
1 2 test1
2 F10 test2

select max(docno::text) as "docno" from table

I woul like that it returns me F10, but it doesn't... how can i tell him to
give me the max value without consider the char part?

You'll have to strip out the letters and convert to integer before
computing the maximum. Something like this:

SELECT max(regexp_replace(docno, '[^[:digit:]]', '', 'g')::integer)
FROM "table"

Yours,
Laurenz Albe

#3

"Laurenz Albe" <invite (AT) spam (DOT) to.invalid> ha scritto nel messaggio
news:1192175917.122666 (AT) proxy (DOT) dienste.wien.at...

Quote:

Dade <cucca77NOSPAM (AT) nospamgmail (DOT) com> wrote:
i have a problem with the max value of a varchar field

id docno description
=================
1 2 test1
2 F10 test2

select max(docno::text) as "docno" from table

I woul like that it returns me F10, but it doesn't... how can i tell him
to
give me the max value without consider the char part?

You'll have to strip out the letters and convert to integer before
computing the maximum. Something like this:

SELECT max(regexp_replace(docno, '[^[:digit:]]', '', 'g')::integer)
FROM "table"

Yours,
Laurenz Albe

Great! Now works perfectly!!!

thanx
Dade

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

max of a varchar field

comp.databases.postgresql comp.databases.postgresql

max of a varchar field - 10-11-2007 , 10:55 AM

Re: max of a varchar field - 10-12-2007 , 02:58 AM

Re: max of a varchar field - 10-12-2007 , 04:55 AM