Re: Conversion of string to int using digits at beginning

On Tue, Nov 18, 2008 at 07:33:47PM +0200, Andrus wrote:
> 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;

Maybe something like:

SELECT MAX(nullif(regexp_replace(test, '^([0-9]*).*$', E'\\1'),'')::INT);

The following parts of the manual may help:

http://www.postgresql.org/docs/current/static/functions-string.html
http://www.postgresql.org/docs/current/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP

Sam