Conversion of string to int using digits at beginning

From: "Andrus" <kobruleht2(at)hot(dot)ee>
To: pgsql-general(at)postgresql(dot)org
Subject: Conversion of string to int using digits at beginning
Date: 2008-11-18 17:33:47
Message-ID: gfuua6$u21$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Table contains CHAR(20) type columns containing numbers and other values.

I need to obtain max integer considering only numbers from start of column
up to first non-integer character.

I tried

create temp table test (test char(20));
insert into test values ('12');
insert into test values ('23/3');
insert into test values ('AX/3');
select max(test::int) from test;

but got

ERROR: invalid input syntax for integer: "23/3 "

How to fix this so that this query returns 23 without error ?

Andrus.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2008-11-18 17:37:45 Re: No serial type
Previous Message Magnus Hagander 2008-11-18 17:31:37 Re: High Availability for PostgreSQL on Windows 2003.